| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional subtotal
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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
Try this...
=SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
Great info re. embedding an add-in. Thanks again!
-- Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
You're welcome!
-- Biff Microsoft Excel MVP "Sarah H." wrote in message ... Great info re. embedding an add-in. Thanks again! -- Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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? -- Regards, Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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? -- Regards, Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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. -- Sarah "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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? -- Regards, Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional subtotal
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/300...-10423159.html IMO, it's one of the better add-ins available. -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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 -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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? -- Regards, Sarah "T. Valko" wrote in message ... 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. ToolsMorefuncEmbed Morefunc in the workbook -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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. -- Sarah "T. Valko" wrote in message ... 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:G330)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... =SUMPRODUCT(SUBTOTAL(2,OFFSET(G3:G33,ROW(G3:G33)-ROW(G3),0,1)),--(G3:G330),G3:G33) -- Biff Microsoft Excel MVP "Sarah H." wrote in message ... 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:g330) (if that would only work). It seems impossible. Thanks for any insight, Sarah |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUBTOTAL | Excel Discussion (Misc queries) | |||
| Conditional Subtotal in table | Excel Discussion (Misc queries) | |||
| conditional subtotal function | Excel Worksheet Functions | |||
| conditional subtotal counting | Excel Worksheet Functions | |||
| Conditional SUBTOTAL | Excel Discussion (Misc queries) | |||
![Sarah H.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
