Conditional subtotal

[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

 

[T. Valko]

Try this...

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

 

[T. Valko]

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))

 

[Sarah H.]

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!

I can share my worksheets without having to
ensure the other users have "morefunc" installed.

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

 

[Sarah H.]

Great info re. embedding an add-in. Thanks again!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Great info re. embedding an add-in. Thanks again!

 

[Sarah H.]

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?

 

[T. Valko]

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

 

[Sarah H.]

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.

 

[T. Valko]

Connecting to the Morefuunc site is pretty erratic. Sometimes you can,
sometimes you can't. Whenever I suggest the add-in to someone I always
include a link to a mirror site where you can download it.

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

IMO, it's one of the better add-ins available.