Google Answers Logo
View Question
Q: Excel - Embed a Sumif in a Sumtotal function ( Answered 5 out of 5 stars,   0 Comments )
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:
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)?


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:5 out of 5 stars
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

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

Good luck with your work.

gte866f-ga rated this answer:5 out of 5 stars

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy