Counting in a filtered column

  • Thread starter Thread starter greg7468
  • Start date Start date
G

greg7468

Hello all,
I am looking for a way of counting the amount of times "PASS" appear
in column B wether or not column A is filtered. I have trie
countif(B1:B10,"PASS") but when column A is filtered the total cel
counting "PASS" in column B still shows the overall total. I need it t
only show how many "PASS" are now in column B.

Thanks in advance. Keep up the great work
 
Don,
thank you for you help, I sort of understand the SubTotal in HELP but
how do I subtotal column B so that after filtering column A it only
counts how many "PASS" there are. At present if I subtotal column B it
still counts all the "PASS" and "FAIL" I need it to subtotal only the
"PASS" after filtering.

Greg.
 
By "now in column B" I assume you mean "now visible in column B". If so,
here's one way:

=SUMPRODUCT(--(rng="PASS"),SUBTOTAL(3,OFFSET(INDEX(rng,1,1),
ROW(INDIRECT("1:" & ROWS(rng)))-1,0)))

where rng is your target range (B1:B10 in this case). If you want to
hard-code your range:

=SUMPRODUCT(--(B1:B10="PASS"),SUBTOTAL(3,OFFSET(B1,
ROW(INDIRECT("1:10")-1,0)))
 
Back
Top