COUNTIF when filter applied to table.

1. COUNTIF when filter applied to table.

I'm trying to write my formula to update my totals when I apply a filter to the column.

Currently I'm using COUNTIF(\$B\$17:\$B\$1000,"1")

"1" in this instance is a name and NOT intended to be a value. I need the Sum to land in B3.

I've been trying to use =SUMPRODUCT with no success. Unfortunately, this is a new formula for me.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$B\$17:\$B\$1000,ROW(\$B\$17:\$B\$1000)-ROW(\$B\$17),0,1)),--(\$B\$17:\$B\$1000="1"))

When using this formula it returns a total of "0"

Any help would be greatly appreciated.

2. Re: COUNTIF when filter applied to table.

=SUMPRODUCT((Append1[Location]=[@Zone])*(SUBTOTAL(3,OFFSET(\$B\$17,ROW(Append1[Location])-MIN(ROW(Append1[Location])),0))))

3. Re: COUNTIF when filter applied to table.

This formula seems to work but it zeros out the Totals in Row 13 for some reason.

Thanks so much for your help!

4. Re: COUNTIF when filter applied to table.

Didn't notice that & not sure why it's doing it.

There are currently 1 users browsing this thread. (0 members and 1 guests)