MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I don't want to count cells that I can't see


Posted by A Miller on August 30, 2001 11:17 AM

I am using the Filtering function under the data menu to narrow down items in a column. I have a summary cell that uses the "Countif" command to count True and False in the last column of my data. My summary cell is set to count all 100 cells in the column. However when I have narrowed my selection down I may only have 5 rows of data showing meaning I only have 5 trues of falses, however it still counts all 100 cells even though you can only see 5. How can I make a "Countif" command count only those cells visible to me?


Posted by Barrie Davidson on August 30, 2001 11:24 AM

Assuming you are counting cells in A2:A100, use the subtotal function.

=SUBTOTAL(2,A2:A100)

Regards,
Barrie

Posted by Barry Katcher on August 30, 2001 1:29 PM

Or - use the Advanced filter function and copy to another range, and then total that range.