Sum Text in only visible cells

[asueps]

I currently have the formula =SUMPRODUCT(COUNTIFS(3,OFFSET(I3,ROW(3:235)-5,))*(I3:I235="YES")), however, when I filter through the data and I only want it to count the cells that have "YES" when it is filtered, not just all the cells, I still get all cells with “YES”.

 

[Domenic]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I3:I235,ROW(I3:I235)-ROW(I3),0,1)),--(I3:I235="YES"))

To exclude manually hidden rows as well, replace the 3 in red with 103.

Hope this helps!

 

[asueps]

That didn't work either. I received a #VALUE ! error

 

[Domenic]

Can you post the exact formula you're using?

 

[asueps]

Hi Domenic - When I plugged that formula in, I received a #VALUE ! error.

 

[asueps]

=sumproduct(subtotal(3,offset(i3:i235,row(i3:i235)-row(i3),0,1)),--(i3:i235="yes"))

 

[Domenic]

Does your range have one or more cells that contain the error #VALUE! in it?

 

[asueps]

No - Just the cell that the formula is is. Cell I238

 

[asueps]

This is the full error "SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 233, column count: 1."

 

[Domenic]

=sumproduct(subtotal(3,offset(i3:i235,row(i3:i235)-row(i3),0,1)),--(i3:i235="yes"))

The syntax for this formula is correct, and the range sizes are the same.