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.

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