Use Value from separate cell in COUNTIF formula

try:

Please Login or Register  to view this content.

Add "" for your text, then for name range, have to use Indirect.

This formula works:
=COUNTIFS(ED_Date,">"&$F$1,ED_TO,&"<>")

This formula does not:
=COUNTIFS($A$1&"_Date",">"&$F$1,Indirect($A$1&"_TO"),&"<>")

The INDIRECT function converts the text string $A$1&_Date that is in your formula that doesn't work into a REFERENCE that Excel can use which in this case is a named range.

Maybe this will help in understanding:

	A	B	C	D	E
1	Named Ranges			NAME:	joe
2	Ed	Joe
3	1	2		Sum
4	2	4		30	=SUM(INDIRECT(E1))
5	3	6		0	=SUM(E1)
6	4	8
7	5	10