# Countif with Filter- multi-column range?

#### jkeyes

##### Active Member
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))

### 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.

##### MrExcel MVP
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
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! ##### MrExcel MVP
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! 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:



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

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



{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:

 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.

Replies
9
Views
185
Replies
1
Views
83
Replies
10
Views
215
Replies
3
Views
119
Replies
2
Views
31