# Sum Product?

#### gomukuma

##### New Member
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)
 LTM LTM PYTM PYTM Customer No / Month Aug-14 Jul-14 Jun-14 May-14 1468 50 100 75 75 1206 200 30 125 100

<TBODY>
</TBODY>

Summary - formula required (sheet 2)
 Customer No LTM PYTM 1468 =Sum of Cust 1468 for LTM =Sum of Cust 1468 for PYTM 1206 =Sum of Cust 1206 for LTM =Sum of Cust 1206 for PYTM

<TBODY>
</TBODY>

I would really appreciate if someone could help.

Thank you!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Formula in B7 copied down and across:

Excel 2010
ABCDE
1LTMLTMPYTMPYTM
2Customer No / MonthAug-14Jul-14Jun-14May-14
31468501007575
4120620030125100
5
6Customer NoLTMPYTM
71468150150
81206230225
Sheet1
Cell Formulas
RangeFormula
B7=SUMPRODUCT((\$A\$3:\$A\$4=\$A7)*(\$B\$1:\$E\$1=B\$6)*(\$B\$3:\$E\$4))

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.

Last edited:
There's no criteria for Customer No there Rick.

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.

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.

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

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

That's pretty common in my experience.

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.

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!

Replies
2
Views
184
Replies
10
Views
512
Replies
1
Views
271
Replies
4
Views
436
Replies
7
Views
594

1,221,241
Messages
6,158,736
Members
451,513
Latest member
EbenAgya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back