Subtotal Count Words!!

**deftones200** · 07-31-2007, 10:20 AM

Hi,

I have a column "completed" which either contains the words "yes" or "no". I also have an auto filter on this column and need a subtotal function that will return the amount of "yes" when filtered.

Any ideas?

**BigBas** · 07-31-2007, 10:25 AM

You can always use a COUNTIF function:

=COUNTIF(A1:A100,"YES")

(Adjust range to suit). Let me know if that helps.

**Ron Coderre** · 07-31-2007, 10:37 AM

Try using the SUBTOTAL function.....

If your filtered data is in A5:A100 (with A5 as the col heading)
this formula counts the number of non-blank visible filtered cells:
A4: =SUBTOTAL(3,A6:A100)

Other options for the first argument of SUBTOTAL are:
Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Does that help?

**deftones200** · 07-31-2007, 10:42 AM

i'm farmilia with the SUBTOTAL function, the prolem i have is subtotalling the "yes" when the filter is on.

**Ron Coderre** · 07-31-2007, 11:06 AM

I guessed that you filtered to display "yes" values....Evidently, no.

If the data with yes/no values is in B1:B100, with B1 as the col heading

This formula counts the occurrences of "yes" in the visible filtered range:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1))*(B2:B100="yes"))

Note: there are NO spaces in that formula

Is that something you can work with?