Counting in a filtered column

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
 
G

greg7468

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.
 
J

JE McGimpsey

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)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

best way to count something in one column using criteria in another column 2
Counting unique values in multiple columns 4
Help with column accumulation needed 2
Need to count no. of rows where cell in column A = "value1" AND cellin column B (same row) = "value2 2
Formula - count Nº of entries but with other filtering criteria 2
need help on either doing a loop and counting specific values orusing subtotal function 2
Counting all "F"s in column A where there is also an "A" in column 5
Excel 2007 custom filter 4

Top