# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.

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!

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:

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

Replies
2
Views
358
Replies
25
Views
781
Replies
14
Views
288
Replies
21
Views
844
Replies
3
Views
193

1,207,278
Messages
6,077,501
Members
446,286
Latest member
ropebender

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

### Which adblocker are you using?

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

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