ROW(INDIRECT(MIN(A2:A14)&":"&MAX(A2:A14))) is not working when over 6 numbers long

[BradleyS]

My formula doesn't work when the numeric ID is over 6 numbers long. It shows a #REF! error
=SUMPRODUCT(SIGN(FREQUENCY(IF((B2:B14="Female")*(C2:C14>=19)*(D2:D14="Current"),A2:A14),ROW(INDIRECT(MIN(A2:A14)&":"&MAX(A2:A14))))))

I'm trying to get a count of the unique ID's based on the other criteria, but this section of the formula is not working: ROW(INDIRECT(MIN(A2:A14)&":"&MAX(A2:A14)))

Is there another formula alternative I can use to get the same result?

ID	Sex	Age	Status
1234567890​	Female	20	Current
1234567890​	Female	20	Current		#REF!​
6789456123​	Male	25	Current
6789456123​	Male	25	Current
6789456123​	Male	25	Current
5678912340​	Male	14	Out
5678912340​	Male	14	Out
4567893210​	Female	24	Current
4567893210​	Female	24	Current
3456789012​	Female	22	Current
3456789012​	Female	22	Current
2345678901​	Female	25	Current
2345678901​	Female	25	Current

 

[RoryA]

You can't use INDIRECT with numbers that large since there are only just over 1m rows available. you could try something like:

=SUMPRODUCT(SIGN(FREQUENCY(IF((B2:B14="Female")*(C2:C14>=19)*(D2:D14="Current"),MATCH(A2:A14,A2:A14,0)),MATCH(A2:A14,A2:A14,0))))

assuming you're still using 2010.

 

[BradleyS]

Perfect. Thank you