Excel Functions

[John Jan]

I like the "Auto Filter" feature, especially with
a "Subtotal" at the end of several columns of my
spreadsheet. What I need know is something like a "SUMIF"
feature exists for "Subtotal" Since I have a lot of
columns on my spreadsheet I found SUMIF does the
conditional sum, but it includes both visual and hidden
fields.

 

[Guest]

Try a pivot table

 

[Peo Sjoblom]

Surely you could filter for the single sumif criteria and use
subtotal? Can you post how a sumif formula would look like
if you did it on an unfiltered list? I mean using custom you can
filter on multiple criteria.. Another way is to include a help column
that will return true or false for the sumif criteria and filter on true
and use subtotal(9,range)

 

[Peo Sjoblom]

Here's one way that would sumif visible cells

=SUMPRODUCT((SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$20)-MIN(ROW($B$2:$B$20)),,1))*
($B$2:$B$20>=5))*($B$2:$B$20))

it will sum values in B2:B20 that are greater or equal to 5

So if you for instance first filter on column A and you'll get a filtered
list and the above
will return the conditional sum. However you might as well filter custom on
B (greater than or equal to 5) and then use the subtotal(9,range)