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

			Example 1
Named Range		This formula works
302	303		302		1		Formula is =COUNTIF(Range1,D5)
455	654		Example 2
480	409		I would like this formula to work
110	995		302	Range1	0		Formula is =COUNTIF(E8,D8)
781	861						(I have referenced E8 in the formula but it doesn't work)
235	582
185	991

<colgroup><col span="6"><col><col span="6"></colgroup><tbody>
</tbody>

<colgroup><col span="6"><col><col span="6"></colgroup><tbody>
</tbody>

 

[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!!