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

excel excel-formula worksheet-function
Ajedi32 picture Ajedi32 · Jan 14, 2013 · Viewed 48k times · Source

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?

Answer

barry houdini picture barry houdini · Jan 14, 2013

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

Related questions

Convert date field into text in Excel

I have an Excel file which has a column formatted as date in the format dd-mm-YYYY. I need to convert that field to text. If I change the field type excel converts it to a strange value (like 40603). I tried …

Read More
Refer to a cell in another worksheet by referencing the current worksheet's name?

I plan to have a workbook with 24 sheets. The sheet names will be: Jan, Jan item, Feb, Feb item, etc. Basically it's for budgeting, with the month named sheet having summary info, and the ones with "item" in the name …

Read More
How to use VLOOKUP result as COUNTIF criteria

I want to be able to determine how many times a person's particular ID (e.g. #123) has appeared in a separate column (e.g. column C) given a name (e.g. Bob). I have: Let column A consist of several …

Read More