Search code examples
excelexcel-formulaworksheet-function

How do I only count visible rows when using the COUNTIFS function?

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?

Solution

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