I'm having a hard time with my sumif/sumproduct formulas. From the data
below, if I enter the following formula:

=SUM(IF((B2:B7="A")*(C2:C7="July"),D2:D7)) CTRL + SHFT + ENTER

I get the answer of $150 - which is what I want. However, this table gets
bigger with every transaction and every month. If, in the previous formula,
I try to include a bigger range than what actually has info in it, it returns
#VALUE. I don't want to keep changing my formula each month. What can I do
to include blank cells i.e. a range of A2:A100?

PS I tried Sumproduct as well and it produced the same results.

Product Customer Month Amount
Pizza A July $150
B July $200
C July $100
Drinks A August $300
B August $250
C August $50

Thanks
Peanut

What can I do to include blank cells i.e. a range of A2:A100?

With blank cells below in rows 3 to 100,
I could get an extended range to work using your original array formula:
=SUM(IF((B2:B100="A")*(C2:C100="July"),D2:D100))

Perhaps just do a check on rows 3 to 100, especially for cols B and C.
Are there any stray formulas returning #VALUE! somewhere?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peanut" wrote:
I'm having a hard time with my sumif/sumproduct formulas. From the data
below, if I enter the following formula:

=SUM(IF((B2:B7="A")*(C2:C7="July"),D2:D7)) CTRL + SHFT + ENTER

I get the answer of $150 - which is what I want. However, this table gets
bigger with every transaction and every month. If, in the previous formula,
I try to include a bigger range than what actually has info in it, it returns
#VALUE. I don't want to keep changing my formula each month. What can I do
to include blank cells i.e. a range of A2:A100?

PS I tried Sumproduct as well and it produced the same results.

Product Customer Month Amount
Pizza A July $150
B July $200
C July $100
Drinks A August $300
B August $250
C August $50

Thanks
Peanut

See this screencap:

http://img504.imageshack.us/img504/5203/sumpsr2.jpg

As you can see SUMPRODUCT does work even though the ranges referenced
include empty cells.

Each referenced range has to be the same size.

=SUMPRODUCT(--(B2:B20="A"),--(C2:C20="July"),D2:D20)

You can't do something like this:

=SUMPRODUCT(--(B2:B100="A"),--(C2:C50="July"),D2:D50)

Or this (unless you're using Excel 2007):

=SUMPRODUCT(--(B:B="A"),--(C:C="July"),D:D)

--
Biff
Microsoft Excel MVP


"Peanut" wrote in message
...
I'm having a hard time with my sumif/sumproduct formulas. From the data
below, if I enter the following formula:

=SUM(IF((B2:B7="A")*(C2:C7="July"),D2:D7)) CTRL + SHFT + ENTER

I get the answer of $150 - which is what I want. However, this table gets
bigger with every transaction and every month. If, in the previous
formula,
I try to include a bigger range than what actually has info in it, it
returns
#VALUE. I don't want to keep changing my formula each month. What can I
do
to include blank cells i.e. a range of A2:A100?

PS I tried Sumproduct as well and it produced the same results.

Product Customer Month Amount
Pizza A July $150
B July $200
C July $100
Drinks A August $300
B August $250
C August $50

Thanks
Peanut

Sorry, it should have read as: rows 8 to 100, not rows 3 to 100
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That worked. I had a stray value in my table that was causing the error.
Thank you for your help!

"Max" wrote:

What can I do to include blank cells i.e. a range of A2:A100?

With blank cells below in rows 3 to 100,
I could get an extended range to work using your original array formula:
=SUM(IF((B2:B100="A")*(C2:C100="July"),D2:D100))

Perhaps just do a check on rows 3 to 100, especially for cols B and C.
Are there any stray formulas returning #VALUE! somewhere?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peanut" wrote:
I'm having a hard time with my sumif/sumproduct formulas. From the data
below, if I enter the following formula:

=SUM(IF((B2:B7="A")*(C2:C7="July"),D2:D7)) CTRL + SHFT + ENTER

I get the answer of $150 - which is what I want. However, this table gets
bigger with every transaction and every month. If, in the previous formula,
I try to include a bigger range than what actually has info in it, it returns
#VALUE. I don't want to keep changing my formula each month. What can I do
to include blank cells i.e. a range of A2:A100?

PS I tried Sumproduct as well and it produced the same results.

Product Customer Month Amount
Pizza A July $150
B July $200
C July $100
Drinks A August $300
B August $250
C August $50

Thanks
Peanut

Aha, glad that was it <g!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Peanut" wrote in message
...
That worked. I had a stray value in my table that was causing the error.
Thank you for your help!