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:

=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)))

<colgroup><col style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;" width="61"> <tbody>
</tbody>

SHEET1	Day #1	Daily Sales	MTD Sales	YTD Sales
Product1		25
Product2		19
Product3		18
Etc

<tbody>
</tbody>

SHEET2	Date	1/2/2012	1/3/2012	1/4/2012	1/5/2012	1/6/2012	1/7/2012	1/8/2012	1/9/2012
		1	2	3	4	5	6	7	8
Region1	Product1	20	10	15	10	10	10	10	10
Region1	Product2	13	15	15	15	15	15	15	15
Region1	Product3	10	5	5	5	5	5	5	5
Region2	Product1	5	1	1	1	1	1	1	1
Region2	Product2	6	6	6	6	6	6	6	6
Region2	Product3	8	12	12	12	12	12	12	12
Etc	Etc
Etc	Etc

<tbody>
</tbody>

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.

SHEET1	Day #1	Day #2	Day #3
Product1	25	11	16
Product2	19	21	21
Product3	18	17	17

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

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