Filtering effects on a SUMIF formula

**Flylloyd** · 03-24-2012, 01:24 PM

I have several SUMIF formula is my spreadsheet. The are fine when I do not turn on any filters. However, if I filer our some cell ranges, the SUNIF formula ignores what I have filters out and sums the enitre sheet including hidden data. Can this be addresed? Thanks, Lloyd

**daddylonglegs** · 03-24-2012, 01:32 PM

If you are using a formula like this

=SUMIF(A2:A100,"x",B2:B100)

that sums column B when column A = "x"

to make that sum only visible rows after filtering change to

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B100)-ROW(B2),0))*(A2:A100="x"))

**Flylloyd** · 03-24-2012, 02:57 PM

Appreciate the quick response, but I'm getting an error message. My formula is =SUMIF(D1:D103,"N",E1:E103). Would the sumprodut you suggest work with a simple =SUM(E2:E98) formula as well when using filtering? Thanks again, Lloyd

**daddylonglegs** · 03-24-2012, 04:04 PM

So for that SUMIF my suggestion would become

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E1,ROW(E1:E103)-ROW(E1),0))*(D1:D103="N"))

For a simple SUM it's not so complex, you can use SUBTOTAL on its own, i.e. instead of =SUM(E2:E98) you can use

=SUBTOTAL(9,E2:E98)