Menu
I've been using Excel's COUNTIFS function to count the number of rows in a table that meet certain criteria, E.g:
=COUNTIFS(Table1[Result],"Fail", Table1[Comments], "")
Now I want to modify this expression so that it only counts rows in Table1 that are visible. (I.E. Not filtered out.) How can I accomplish this?
252
Count ignore hidden cells and rows with excel functions Select a blank cell you will place the counting result into, type the formula =SUBTOTAL(102,C2:C22) (C2:C22 is the range where you want to count ignoring manually hidden cells and rows) into it, and press the Enter key.
Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.
Simple way is to add another column to table - e.g. called helper with a formula like this
=SUBTOTAL(103, B2)
where column B is Result column
Now change formula to
=COUNTIFS(Table1[Result],"Fail", Table1[Comments], "",Table1[Helper],1)
the subtotal formula only returns 1 on visible rows
Without a helper column you can use this formula
=SUMPRODUCT((Table1[Result]="Fail")*(Table1[Comments]=""),SUBTOTAL(103,OFFSET(Table1[Result],ROW(Table1[Result])-MIN(ROW(Table1[Result])),0,1,1)))
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
