Countif with Filter- multi-column range?

[jkeyes]

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!

 

[Aladin Akyurek]

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]

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!

 

[Aladin Akyurek]

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!

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.