Help Please: SUMIF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

baz - 21 Nov 2005 19:33 GMT

I am using Excel 2003.

I have one SUMIF function that works just fine. However I wan to add a
second argument to work in conjunction with the first.

Example: =SUMIF (D1:D200, Fred, Totals) works fine. I want to use this
in conjunction with looking at E1:E200 where criteria equals 54321,
and still summing the "Totals" sum_range.

Fred may have 75 returns with a total of 423 using the first example
but only 34 returns when I look for the second criteria with a total
of 65.

I tried nesting but I'm just not getting it.

I'd appreciate any help in getting.

Please post as email is bogus to prevent spam.

Thanks in advance for any help.

baz

Reply to this Message

Daniel Bonallack - 21 Nov 2005 19:48 GMT

Baz

The sumproduct formula will change your Excel life...

=sumproduct((D1:D200="Fred")*(E1:E200=54321)*F1:F200)

You can have any number of conditions.

You might also want to learn how to set up dynamic name ranges, so you don't
need to worry about extending the formula ranges when new data is added.

An example might be (where column A contains no blank cells, and you have
headers in row 1):
dName: =offset(Sheet1!$A$2,0,0,counta($A:$A)-1)
dZipCode: =offset(Sheet1!$B$2,0,0,counta($A:$A)-1)
dTotal: =offset(Sheet1!$C$2,0,0,counta($A:$A)-1)

Then your formula would be:
=sumproduct((dName="Fred")*(dZipCode=54321)*dTotal)

Hope this helps. I'm sure there are experts out there who would set it up
differently, but this small extra knowledge made a big difference to me.

Daniel

> I am using Excel 2003.
>
[quoted text clipped - 18 lines]
>
> baz

Reply to this Message