View Question
Q: Excel - Embed a Sumif in a Sumtotal function ( Answered ,   0 Comments )
 Question
 Subject: Excel - Embed a Sumif in a Sumtotal function Category: Computers > Algorithms Asked by: gte866f-ga List Price: \$5.00 Posted: 06 Nov 2006 05:36 PST Expires: 06 Dec 2006 05:36 PST Question ID: 780478
 ```I am using filters. I need to find a equivalent to an embedded sumif in a sumtotal. I want a function like this: Column A are people's names Column B is a fruit type Column C is a quantity Column D is a month. I filter column D to match the onth I am interested in. then, I want: SUMTOTAL(Sumif(b1:b10,="text",c1:c10) where b1:b10 is various "text" (i.e. apple, berry, pear) and c1:c10 contain quanitities i want to sum. I know I can do additional filtering. I don't want to do that. I want to have a subtotal(sumif) for each fruit depending on what month I chose to filter.``` Request for Question Clarification by maniac-ga on 06 Nov 2006 17:36 PST ```Hello Gte866f, There does not seem to be a direct way to calculate what you have asked for with sumif or subtotal. There are a couple alternatives that will provide the same result. [1] Use the DSUM function instead with a pair of criteria for the fruit and month. This worked OK in my testing by entering the fruit name / month in the criteria fields. It also may be possible to select the fruit and month from a list (similar to the auto filter) to do what you want. [2] Use a Visual Basic macro to sum the rows that are not hidden by the filter (with the fruit name specified). This can be run automatically whenever the filter is changed. Would you accept an answer that uses one of these methods, and if so which one (or both)? Thanks. --Maniac``` Clarification of Question by gte866f-ga on 07 Nov 2006 00:10 PST ```ok. The DSUM is exactly what I needed. Thanks for the Tip. First time users... How do I pay?``` Clarification of Question by gte866f-ga on 07 Nov 2006 01:17 PST ```Can you also tell me how to make the second filed in the DSUM function a cell reference rather than actual "text" ?``` Clarification of Question by gte866f-ga on 07 Nov 2006 07:24 PST `Ok. Everything is taken care of. Thanks for the answer.`
 Subject: Re: Excel - Embed a Sumif in a Sumtotal function Answered By: maniac-ga on 07 Nov 2006 18:46 PST Rated:
 ```Hello Gte866f, Apparently you've figured it out, but for others, let me explain the DSUM function, how it can be used to solve your problem, and some information related to finding further information about Excel functions. First, DSUM takes three parameters: o database - the range of cells holding the "data". The first row has the labels for each column. o field - the column label (as text) or column number with the data value to extract o criteria - the range of cells indicating the values to perform the selection. The first row of each column has a label (to match the labels in the database) and the second row has a value to match. All columns must match the corresponding items in a row for the values in the "field" column to be summed. The example I used was =DSUM(A1:D10,3,F1:G2) where the sample data was in cells A1:D10, the third column was the quantity column, and cells F1:G2 were the criteria I used. As an alternative, you can also use =DSUM(A1:D10,F7,F1:G2) where I stored the text "Quantity" in cell F7 to refer to the same column as described above. Using Excel help, a search with a function name such as DSUM will bring up a direct reference to the function definition along with some related pages of information. For information stored in a "database", the search phrase database will provide a link to DSUM as well as a number of other related database functions and informational pages. Searching online (with a search engine like Google), extend the phrase to something like dsum excel example to find informational pages such as http://www.ozgrid.com/Excel/sum-if.htm which starts with a SUMIF example and includes DSUM as one of a few alternatives with good examples and spreadsheets to download. If any part of the answer is unclear or you need further information, please make a clarification request. I would be glad to answer further. Good luck with your work. --Maniac```

 There are no comments at this time.