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:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]

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!