COUNTIF to ignore hidden rows

[Roxhan]

Hello Excel gurus,

I have a large table, which I'm trying to extract reports from. One column lists the 'rounds of amends' for each job (row), eg zero, 1, 2... 15+.
I'd like to know how many jobs had zero amends, 1, 2 etc, and used this formula

=COUNTIF(AE$7:AE$500,"Zero")

(Changing "Zero" to "1", "2" and so forth to get all the different subtotals).

However, I have filtered out rows which aren't relevant to me, but looks like the COUNTIF still includes those rows in the total.

Can anyone help me adjust the above code to ignore hidden rows?
Many thanks in advance for any advice
Rox

 

[bosco_yip]

Hi Roxhan,

Please forward us an example file with data and expected result

And,

Instead of your formula=COUNTIF(AE$7:AE$500,"Zero"), try to use the below formula to ignore hidden rows:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AE$7,ROW(AE$7:AE$500)-MIN(ROW(AE$7:AE$500)),0))*(AE$7:AE$500="Zero"))

Regards

 

[Roxhan]

Hi Roxhan,

Please forward us an example file with data and expected result

And,

Instead of your formula=COUNTIF(AE$7:AE$500,"Zero"), try to use the below formula to ignore hidden rows:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(AE$7,ROW(AE$7:AE$500)-MIN(ROW(AE$7:AE$500)),0))*(AE$7:AE$500="Zero"))

Regards

Hi @bosco_yip

Many thanks for your help! Please find attached an example table.
You'll see in Col H (highlighted yellow) I'm trying to count the number of projects which have '2' rounds of amends, except it's counting hidden rows.
I tried using the Sumproduct formula you supplied, but it's showing up as 0?

Any help would be greatly appreciated
Thanks again
Rox

 

[pecoflyer]

I tried using the Sumproduct formula you supplied, but it's showing up as 0?

It shows zero because your last part of your formula is *(C$6:C$12="2")). 2 between double quotes is text. Remove the double quotes and the answer is 2

 

[bosco_yip]

Hi @bosco_yip

Many thanks for your help! Please find attached an example table.
You'll see in Col H (highlighted yellow) I'm trying to count the number of projects which have '2' rounds of amends, except it's counting hidden rows.
I tried using the Sumproduct formula you supplied, but it's showing up as 0?

Any help would be greatly appreciated
Thanks again
Rox

Hi Roxhan,

Further to pecoflyer's explanation, please see example show below :

1] If criteria is a text value, like "Zero" as per below example, in cell I5 formula use:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(C$6,ROW(C$6:C$12)-MIN(ROW(C$6:C$12)),0))*(C$6:C$12="Zero"))

2] If criteria is a numeric value, like 2 as per below example, in cell I6 formula use:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(C$6,ROW(C$6:C$12)-MIN(ROW(C$6:C$12)),0))*(C$6:C$12=2))



Regards

 

[pecoflyer]

You might also want to refrain form manual aligning columns from which data is extracted for formulas.
Why? Because text is always left-aligned in cells and numbers right-aligned. You can then immediately see when what looks like a number is text when left-aligned, and thus needs some extra work to make it a real number

 

[Roxhan]

Hi @pecoflyer and @bosco_yip
Thank you so much for your follow up answer - that makes total sense, and confirming it's working as expected.
I also appreciate the extra note around formatting my cells, so it's easier to differentiate between real numbers versus text, I'll bear that in mind in the future!

Many thanks again, so excited to run these numbers now haha
Rox

 

[bosco_yip]

Hi @pecoflyer and @bosco_yip
Thank you so much for your follow up answer - that makes total sense, and confirming it's working as expected.
I also appreciate the extra note around formatting my cells, so it's easier to differentiate between real numbers versus text, I'll bear that in mind in the future!

Many thanks again, so excited to run these numbers now haha
Rox

You are welcome