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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,079
Messages
5,570,083
Members
412,310
Latest member
mark884
Top