Help. A twist on summing visible rows

[Jmbostock]

I've been bashing my head against the wall trying to figure this on
out.

2 questions.

1) I want to sum an autofiltered row. This i can do. The problem lie
in that the filtered results contain both text and numerical values.
want to sum only the numbers. When i try to do that it gives m
#VALUE!. Help.

2) I have a row of autofiltered information where i want to count th
rows. Again, this i can do. This time there are duplicate values, whic
i don't want to count. I tried using a function combined wit
FREQUENCY/MATCH, but at this point i've been starring at it too long.

Save my brain.

Thank

 

[Peo Sjoblom]

I can't reproduce that unless I put a value error in the column,
Are you using subtotal?

=SUBTOTAL(9,B2:B50)

will sum B2:B50 filtered regardless of text.

To count unique entries use a help column, assume you want to to count
A2:A50 after you have filtered it and you want the unique count

use a help column and a formula like

=COUNTIF($A$2:A2,A2)=1

now copy down, filter on A and then filter on the help column using TRUE
then count the visible cells in A

=SUBTOTAL(3,A2:A50)