count unique items in list, where several criteria true

[baitmaster]

Good afternoon all

I am using this formula:

=COUNT(1/FREQUENCY(IF(F18:F24=D28,E18:E24),E18:E24))
entered as array formula shift + ctrl + enter

This counts unique entries in column E, where column F matches D28. This is fine when I am counting values in column E, but I need to look at text strings instead. This formula does not work for text strings. Does anyone have an alternative, or a reason why not?

Thanks

 

[dantheman9]

would COUNTA work?

 

[dmadeka]

You may want to try the sumproduct route. Daniel covers it very well in his blog so I wont repeat

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

 

[baitmaster]

thanks for the responses so far

I tried CountA, but it gives the wrong answer. I'm not entirely sure what is happening within the rest of the calculation, but I don't think this is the item that needs to be changed

I also tried sumproduct. I have the following formula, that calculates the number of unique entries in the list:
=SUMPRODUCT((E18:E24<>"")/COUNTIF(E18:E24,E18:E24&""))
Unfortunately this doesn't take account of the additional criteria in column F. I tried changing the COUNTIF to COUNTIFS in order to include this additional range, but again without success