Difficult Calculations

[Amber]

I am needing to combine multiple functions, I believe, but am in need of some
help. I currently have a document that I run a sumproduct. With the
calculations that I now need, I am not sure how to combine the sumproduct
with a sumif function to obtain totals.

My data sampling......

Date Creator Order # PreTaxed Amount
1/1/2007 XIAPPLXRP110 413457 231.54
1/17/2007 AVISD 413558 78.98
1/28/2007 SMITHA 413563 149.74
2/2/2007 XIAPPLXRP110 413573 42.41
2/5/2007 AVISD 413575 38.54
3/4/2007 HILTONA 413580 103.48
4/6/2007 AVISD 413584 28.37
9/1/2007 XIAPPLXRP110 413586 62.72

My current calculation: =SUMPRODUCT((MONTH('Raw Data
2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110"))
This calculation is to determine how many orders we have coming in via
XIAPPLXRP110 each month.

Now I would like to add another layer...I want to know the totals of the
orders coming in via XIAPPLXRP110, by the month rather than just counting the
number of orders.

Any help on this would be great. I need to get this ASAP for my emloyer.

 

[ilia]

Try this:

=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!
B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D264868))

 

[Teethless mama]

=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data
2007'!B2:B64868="XIAPPLXRP110"),'Raw Data 2007'!D264868)

 

[Amber]

I tried this but received #N/A

Not sure why.

=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data
2007'!B2:B64868="XIAPPLXRP110"),'Raw Data 2007'!D264868)

 

[Amber]

This only gave me #N/A. Not sure why!

Try this:

=SUMPRODUCT((MONTH('Raw Data 2007'!A2:A64868)=1)*('Raw Data 2007'!
B2:B64868="XIAPPLXRP110")*('Raw Data 2007'!D264868))

 

[ilia]

Well, both our responses are pretty much identical, so that stands to
reason they both returned the same error.

The only way I can think of to get an #N/A error out of a SUMPRODUCT
is if your number of rows doesn't match up. Double-check the formula
and make sure that all references start on row 2 and end on row 64868.

FWIW, I used your sample data and got what appear to be correct
answers from either formula.

 

[Amber]

Well, both our responses are pretty much identical, so that stands to
reason they both returned the same error.

The only way I can think of to get an #N/A error out of a SUMPRODUCT
is if your number of rows doesn't match up. Double-check the formula
and make sure that all references start on row 2 and end on row 64868.

FWIW, I used your sample data and got what appear to be correct
answers from either formula.

 

[Amber]

Here is the formula.... =SUMPRODUCT((MONTH('Raw Data
2007'!A2:A64868)=1)*('Raw Data 2007'!B2:B64868="XIAPPLXRP110")*('Raw Data
2007'!D264868))

I am using 2 separate tabs. Could that cause the problem?

 

[ilia]

Following Biff's stategy, here it is in action:

http://www.44lbs.net/ilia/sumproduct.jpg

Here is what the source data looks like (some random values):

http://www.44lbs.net/ilia/rawdata2007.jpg

It's populated as far down as your formula calls for:

http://www.44lbs.net/ilia/rawdata2007_tailEnd.jpg

Pivot table confirms formula result:

http://www.44lbs.net/ilia/rawdata2007_pivot.jpg

Hope that helps.