Conditional subtotal

  • Thread starter Thread starter Sarah H.
  • Start date Start date
S

Sarah H.

Hi, guys,

I searched around on the web but I can't find how to do a conditional
subtotal in Excel 2007.

E.g., =subtotal(9,g3:g33>0) (if that would only work). It seems impossible.

Thanks for any insight,
Sarah
 
Try this...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G33>0),G3:G33)
 
Improvement...

Since the sum range and the criteria range are the same we can simplify that
slightly.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G33>0))
 
Terrific, Biff! You did the impossible. :-)

I'm glad you posted the first way also, because I want to apply this to sum
ranges whose criteria ranges are from another column, as well. Works great!

I did meanwhile also find Laurent Longre's "morefunc" Add-In as well. It
solves the problem via a custom function called ARRAY.FILTER.

See http://xcell05.free.fr/morefunc/english/

But I like having your solution, for one, because I can share my worksheets
without having to ensure the other users have "morefunc" installed. Thanks
again! Very slick indeed.
 
You're welcome. Thanks for the feedback!
I can share my worksheets without having to
ensure the other users have "morefunc" installed.
Click to expand...

Actually, you can embed the add-in with the file so others won't have to
have the add-in installed on their machine.

When you install Morefunc it adds a new item to the Tools menu.

Tools>Morefunc>Embed Morefunc in the workbook
 
Below is from a few weeks ago, but I have a further question. I can't find
the "embed-add-in" stuff in Excel 2007. I know I've seen it in XL2002
before, but now I'm using 2007. Any ideas?
 
I have Morefunc v5.06 installed on my machine. This version can't be embeded
in Excel 2007. I don't know if there's a newer version available that will
embed in Excel 2007. Check the Morefunc website
 
Thanks, Biff. That's the version I have too, and as far as I have been able
to determine it is the latest. (Some of the links were dead when I looked
recently.) Much obliged.
 
Back
Top