Countif with Filter- multi-column range?

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
I need to have the countif function work with the filter on. I've found several answers that refer to this situation, though I'm not able to get it to apply to mine. I'm thinking it has to do with either the multi-column range I'm looking in, or the criteria in the countif formula.

Here's my formula: =COUNTIF($U$3:$AO$300, "="&T307)

Here's the version I tried, based on the solutions I found:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($U$3:$AO$300,ROW($U$3:$AO$300)-MIN(ROW($U$3:$AO$300)),,1)),--($U$3:$AO$300=T307))

I get a #VALUE error. Could someone please help me get the correct formula?
Thanks in advance!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
The multi-column range requires a different formula:

=SUM(IF(SUBTOTAL(3,OFFSET($U$3:$AO$300,ROW($U$3:$AO$300)-ROW($U$3),,1)),--($U$3:$AO$300=T307)))

which must be confirmed with control+shift+enter.
 

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
Perfect! Thank you!

For future references to this thread, can you please explain the differences between the two versions (my original attempt for use with a single column range, and yours)? I'd like to understand why/how this works...

Thanks again! :biggrin:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
jkeyes said:
Perfect! Thank you!

For future references to this thread, can you please explain the differences between the two versions (my original attempt for use with a single column range, and yours)? I'd like to understand why/how this works...

Thanks again! :biggrin:

This is going to be a bit technical...

Lets take a smaller multicolumn range $U$3:$V$10 for what follows.

The SumProduct formula you picked up becomes (MIN removed, but that is inessential in what follows)...

=SUMPRODUCT(SUBTOTAL(3,OFFSET($U$3:$V$10,ROW($U$3:$V$10)-ROW($U$3),,1)),--($U$3:$V$10=T307))

of which the SUBTOTAL(3,OFFSET($U$3:$V$10,ROW($U$3:$V$10)-ROW($U$3),,1)) bit evaluates to a vertical vector like:

[1]

{2;2;0;0;2;2;2;2}

and the --($U$3:$V$10=T307) bit evaluates to a matrix like:

[2]

{1,1;1,1;1,0;1,0;1,0;0,0;0,1;0,0}

These two cannot be multiplied using the comma syntax.

When you switch the "star" syntax, that is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($U$3:$V$10,ROW($U$3:$V$10)-ROW($U$3),,1))*($U$3:$V$10=T307))

you'll face a different problem:

[1] consists of 0's (for hidden rows) and counts satisfying >= 1, leading to a wrong result.

If the SubTotal bit would refer to a single column range, say:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($T$3:$T$10,ROW($T$3:$T$10)-ROW($T$3),,1))*($U$3:$V$10=T307))

you would get the correct result with the star syntax.

However, if the star syntax appears to be unavoidable, it's better to switch altogether to a control+shift+entered formula:

{=SUM(IF(SUBTOTAL(3,OFFSET($T$3:$T$10,ROW($T$3:$T$10)-ROW($T$3),,1)),--($U$3:$V$10=T307)))}

or

{=SUM(IF(SUBTOTAL(3,OFFSET($U$3:$V$10,ROW($U$3:$V$10)-ROW($U$3),,1)),--($U$3:$V$10=T307)))}

Note that a non-zero value (like 2) in the condition part of an IF means TRUE, a zero value FALSE, which would make the last formulas intelligible.

Hope this helps.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,128
Messages
5,835,564
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top