Sum Product?

[gomukuma]

Hi.
I am trying to calculate some data, that i think sumproduct should do but i'm struggling with my fomula if anyone can help?

Data (sheet 1)
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD][/TD]
[TD]LTM[/TD]
[TD]LTM[/TD]
[TD]PYTM[/TD]
[TD]PYTM[/TD]
[/TR]
[TR]
[TD]Customer No / Month[/TD]
[TD]Aug-14[/TD]
[TD]Jul-14[/TD]
[TD]Jun-14[/TD]
[TD]May-14[/TD]
[/TR]
[TR]
[TD]1468[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]75[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]1206[/TD]
[TD]200[/TD]
[TD]30[/TD]
[TD]125[/TD]
[TD]100[/TD]
[/TR]
</TBODY>[/TABLE]








Summary - formula required (sheet 2)
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Customer No[/TD]
[TD]LTM[/TD]
[TD]PYTM[/TD]
[/TR]
[TR]
[TD]1468[/TD]
[TD]=Sum of Cust 1468 for LTM[/TD]
[TD]=Sum of Cust 1468 for PYTM[/TD]
[/TR]
[TR]
[TD]1206[/TD]
[TD]=Sum of Cust 1206 for LTM[/TD]
[TD]=Sum of Cust 1206 for PYTM[/TD]
[/TR]
</TBODY>[/TABLE]






I would really appreciate if someone could help.

Thank you!

 

[Andrew Poulsom]

Formula in B7 copied down and across:

Excel 2010
	A	B	C	D	E
1		LTM	LTM	PYTM	PYTM
2	Customer No / Month	Aug-14	Jul-14	Jun-14	May-14
3	1468	50	100	75	75
4	1206	200	30	125	100
5
6	Customer No	LTM	PYTM
7	1468	150	150
8	1206	230	225
Sheet1

Cell Formulas
Range	Formula
B7	=SUMPRODUCT(($A$3:$A$4=$A7)*($B$1:$E$1=B$6)*($B$3:$E$4))

 

[Rick Rothstein]

I think SUMIF may be what you need, something like this maybe...

=SUMIF(Sheet1!$B$1:$E$1,"LTM",Sheet1!B3:E3)

Similar formula for your other "codes". And, of course, you can substitute cell references for the codes if that is more convenient.

 

[Andrew Poulsom]

There's no criteria for Customer No there Rick.

 

[Rick Rothstein]

There's no criteria for Customer No there Rick.

I assumed each customer number was on a row by itself (as shown in the example), so the customer number is controlled by the row its on... the 1468 customer number is on Row 3, hence the summation range of B3:E3.

 

[Andrew Poulsom]

That's fair enough, but I'm not sure that it's safe to assume that the order in one worksheet (Data) will be the same in another worksheet (Summary). The OP may tell us in a reply.

 

[Rick Rothstein]

That's fair enough, but I'm not sure that it's safe to assume that the order in one worksheet (Data) will be the same in another worksheet (Summary). The OP may tell us in a reply.

True, I guess the order could vary, but it didn't in the example the OP posted (of course, it is possible the OP may have just overly simplified things for us also).

 

[Andrew Poulsom]

(of course, it is possible the OP may have just overly simplified things for us also).

That's pretty common in my experience.

 

[Rick Rothstein]

That's pretty common in my experience.

Yeah, tell me about it... it's not like we have never seen that before, huh?

If, however, my interpretation of the data is correct, I would think SUMIF might be more efficient than SUMPRODUCT though.

 

[gomukuma]

Thanks guys, the lists may not be in the same order so sumproduct i think is better.

Something im not sure is relevant, the data im pulling from sheet 1 is help in a pivot table, should that make any difference to the formula?

Much appreciated!