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

Question

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 1

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

Solution 2

I use this formula: =subtotal(3,B2:B100) where subtotal(3, that is CountA and B2:b100 is the range. The hidden rows in a filter are ignored and this formula only counts the visible rows. It works for me and hope it works for you

Comments

[Ajedi32]

That seems to work. Unfortunately, it's also triggering a weird bug in Excel that's making it very difficult to filter the table: answers.microsoft.com/en-us/office/forum/office_2007-excel/… Do you know of any other way of checking for visible rows that I might be able to use to work around this bug?

[barry houdini]

there is a way without helper column - I'll edit my answer in 30 mins, currently on the train (!)

[SeanC]

this explains the problem and the fix - change the SUBTOTAL formula to =--SUBTOTAL(103, B2)

[barry houdini]

+1 Sean, never seen that before. Looks like it's sorted but I posted the formula as promised - that should work without need for a helper column