Count all nonblanks in a column, but exclude duplicates and hidden rows.

[Telefonstolpe]

Hi,

I´m looking for a function that counts all nonblanks in a column, but excludes duplicates and hidden rows.
Been trying for a while to solve this problem with sumproduct- and subtotal functions, but I can't get it to work in the end. I realized it was more difficult than it seemed.

Example of what I'm trying to do:

	A
1	Apple
2	Apple
3	Pear
4	Pear
5	Pear
6
7	Orange
8	Orange
9	Orange

<tbody>
</tbody>

In this example red-marked rows number 3, 4, 5 and 8 are hidden
The function I'm looking for should in this case return "2", one apple plus one orange, and thus exclude duplicates, hidden rows and blanks.

Thanks in advance!
/Chris

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A1:A9,ROW(A1:A9)-ROW(A1),0,1))>0,MATCH("~"&A1:A9,A1:A9&"",0)),ROW(A1:A9)-ROW(A1)+1)>0,1))

Note that rows hidden via the AutoFilter are also taken into consideration.

Hope this helps!

 

[Telefonstolpe]

Thank you very much!

Works perfectly!

It's gonna take me a while to figure out the logic behind this function

Thanks again!

/Chris

 

[Domenic]

You're very welcome!