Formula HELP to Sum Cells with Multiple Criteria

[Wanda13]

In sheet 2 I have a list of product sales by region for 260 days of 2012. In sheet 1 I want to summarize the total of the product on a specific day. The formula I am using does not work.
I want to create a formula in Sheet1 to get total sales for Product1 on Day 1, Product2 on Day 1, etc. This is the formula I have but it is not working:[TABLE="width: 46"]
<colgroup><col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <tbody>[TR]
[TD="class: xl945, width: 61, bgcolor: transparent"]=SUM(IF('Sheet2'!$B$3:$B$10='Sheet1'!A2&$B$1,IF('Sheet2'!$C$1:$C$260='Sheet1'!A2&$B$1,'2012 Sales'!$C$3:$JC$113)))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]SHEET1[/TD]
[TD]Day #1[/TD]
[TD]Daily Sales[/TD]
[TD]MTD Sales[/TD]
[TD]YTD Sales[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD][/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]SHEET2[/TD]
[TD]Date[/TD]
[TD]1/2/2012[/TD]
[TD]1/3/2012[/TD]
[TD]1/4/2012[/TD]
[TD]1/5/2012[/TD]
[TD]1/6/2012[/TD]
[TD]1/7/2012[/TD]
[TD]1/8/2012[/TD]
[TD]1/9/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD]Region1[/TD]
[TD]Product1[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Region1[/TD]
[TD]Product2[/TD]
[TD]13[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Region1[/TD]
[TD]Product3[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Region2[/TD]
[TD]Product1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Region2[/TD]
[TD]Product2[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Region2[/TD]
[TD]Product3[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Etc[/TD]
[TD]Etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc[/TD]
[TD]Etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is much appreciated!

 

[Vidar]

Hi

I would probably use a Pivottable for that task.
Your formula is also referencing a sheet called "2012 Sales". What's on that sheet?

 

[Inzuri]

What version of Excel are you using? The SUMIFS function will do just that.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]SHEET1[/TD]
[TD="width: 64"]Day #1[/TD]
[TD="width: 64"]Day #2[/TD]
[TD="width: 64"]Day #3[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17
[/TD]
[/TR]
</tbody>[/TABLE]

This is the table I have, with SHEET1 in cell A10. Use the formula

=SUMIFS(Sheet2!C:C,Sheet2!$B:$B,$A11)

in cell B11, and then copy/paste it across the range and it should pull the sums you need.

 

[Wanda13]

Thanks Vidar. '2012 Sales' should be 'Sheet2' I missed that one when I tried to make the formular simple.

I found a formula that is finally generating numbers but the numbers are not correct:
=SUMPRODUCT(('Sheet2'!$D$4:$JC$113)*('Sheet2'!$C$4:$C$113='Sheet1'!$C6)*('Sheet2'!$D$3:$JC$3='Sheet1'!$R$4))

D4:JC113 is the sales data in sheet 2
C4:C113 is the Product list in sheet 2
C6 in Sheet1 is the Product to be totalled
R4 is Day #

 

[Wanda13]

Hi Inzuri. I have Excel 2010. I tried that formula and came up with an error.

I am trying to get a formula like: Look in Sheet2 for Product1 on Day #1 and total all Product1 in that column. There are 5 different regions that sell Product 1. If I change the date (Day#) the formula looks for the total on sheet of Product1, Product2, etc.
On Sheet2 below the daily sales data I have calculation for month-to-date and year-to date totals. So if I can get the Daily Sales totals to work I will be able to change the data range for MTD and YTD totals in Sheet 1.
I hope I did not make this more confusing
But thanks for any suggestions you may have.

 

[Vidar]

Is a pivottable out of question?

 

[Wanda13]

Yes, I have more formulas to calculate variance.
Wish I could send you my spreadsheet lol