Countif formula that references a range based on the value of a separate cell

[maclachlan19]

Sorry, I couldn't figure out a better way to present this.
I have a named Range in my spreadsheet.
I want to use a Countif formula. But I want the name of the range to be referenced from another cell.
I can't figure out how to write the formula.
In example 2 below, the cell E8 contains Range1
[TABLE="width: 795"]
<colgroup><col span="6"><col><col span="6"></colgroup><tbody>[TR]
[TD]


[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Example 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Named Range[/TD]
[TD][/TD]
[TD="colspan: 2"]This formula works[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]302[/TD]
[TD="align: right"]303[/TD]
[TD][/TD]
[TD="align: right"]302[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="colspan: 4"]Formula is =COUNTIF(Range1,D5)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]455[/TD]
[TD="align: right"]654[/TD]
[TD][/TD]
[TD="colspan: 2"]Example 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]480[/TD]
[TD="align: right"]409[/TD]
[TD][/TD]
[TD="colspan: 4"]I would like this formula to work[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]995[/TD]
[TD][/TD]
[TD="align: right"]302[/TD]
[TD]Range1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="colspan: 3"]Formula is =COUNTIF(E8,D8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]781[/TD]
[TD="align: right"]861[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"](I have referenced E8 in the formula but it doesn't work)[/TD]
[/TR]
[TR]
[TD="align: right"]235[/TD]
[TD="align: right"]582[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]185[/TD]
[TD="align: right"]991[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 795"]
<colgroup><col span="6"><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Try it this way...

=COUNTIF(INDIRECT(E8),D8)

Cell E8 contains a text string so you need to use the INDIRECT formula to get Excel to convert it to a range reference.

 

[maclachlan19]

Thanks Rick, that work perfect!!