Forum Discussion
request Excel formula help counting only the visible cells when using a filter
I have tried many different ways but can not figure out how to change the following formula to work with a filter. I want to count cells in row DM if the name in column A matches "sample1" and the cell is >0, but I need to only count visible cells when using filters on other columns. How do I add the filter criteria? =COUNTIFS($A$12:$A$400,"sample1",DM$12:DM$400,">0")
4 Replies
Like this:
=SUMPRODUCT(($A$12:$A$400="sample1") * (DM$12:DM$400>0) * SUBTOTAL(103, OFFSET($A$12, ROW($A$12:$A$400)-ROW($A$12), 0)))
Source: Count visible rows with criteria
Thank you Hans, this works when using a filter but the data table I exported must have a hidden format because the blank cells are also being counted, which is why I was trying to only count cells with a value >0. Might you know a workaround to not count the blank cells which are not hidden? When I delete a blank cell, your formula updates to one number lower, so the formula works. I just now need to figure out how to either format my data spreadsheet differently or come up with a formula workaround. Thank you
