CountIf on visible rows only

[DarkJester89]

I have a formula

=COUNTIF(Table1[Column11],10)+COUNTIF(Table1[Column11],11)

Combined countif to look for 10 or 11 in the table

It's on a filtered table, but is there a way to convert this to count only visible rows? I've tried various subtotal formulas but can't get it to work. Thank you in advance.

 

[plettieri]

Yes, you can modify the formula to count only the visible rows by using the SUBTOTAL function instead of the COUNTIF function. The SUBTOTAL function can perform a variety of calculations on a range of cells, including COUNTIF, and it has an option to ignore hidden rows.

Here's the modified formula:

=SUBTOTAL(3,Table1[Column11]=10)+SUBTOTAL(3,Table1[Column11]=11)

In this formula, the first argument of the SUBTOTAL function is set to 3, which corresponds to the COUNTA function. The second argument is the range of cells to count, which is specified using the same syntax as the COUNTIF function. The only difference is that the range is enclosed in parentheses after the equals sign.

The second part of the formula is the same as the first, except that it counts the occurrences of 11 instead of 10.

By using the SUBTOTAL function with the option to ignore hidden rows, this formula will count only the visible rows in the filtered table.

hope this helps
plettieri

 

[Fluff]

How about with a helper column like

Fluff.xlsm
	B															Q
7	Column11															IsVisible
8	10															1
10	0															1
11	0															1
12	0															1
13	0															1
14	10															1
15	11															1
34
35	3
Search

Cell Formulas
Range	Formula
Q8,Q10:Q15	Q8	=SUBTOTAL(3,[@Column11])
B35	B35	=SUM(COUNTIFS(Table1[Column11],{10,11},Table1[IsVisible],1))

 

[DarkJester89]

@plettieri and @Fluff Thank you for the suggestions. I tried both options, but I keep getting stuck where the second table entry isn't recognized and get the following screenshots.

I even tried splitting it as a separate formula for 10 and 11, and also tried manually building the formula to match. In column AL on the workbook, it says its a formula parse error in google sheets.

Is there something I'm doing wrong? Thank you again for your time.

Workbook

 

[arthurbr]

Combined countif to look for 10 or 11 in the table

10 OR 11, or rather 10 AND 11 ?

 

[DarkJester89]

10 OR 11, or rather 10 AND 11 ?

I was trying to go for above 9 to catch anything because it can go higher than 11, but I couldn't figure out the formula to make it match.
10 and 11 would work great though if >9 doesn't work.

 

[Fluff]

What is the name of your table?

 

[arthurbr]

I was trying to go for above 9 to catch anything because it can go higher than 11, but I couldn't figure out the formula to make it match.
10 and 11 would work great though if >9 doesn't work.

So the condition >9 is OK ?

 

[DarkJester89]

What is the name of your table?

Table1

So the condition >9 is OK ?

Yes, that's ok

 

[Fluff]

Ok how about

=COUNTIFS(Table1[Column11],">9",Table1[IsVisible],1)