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]

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?

Did you try it?

 

[gomukuma]

Apologies, when I try and pull the data from the pivot table i get #N/A

Did you try it?

 

[Andrew Poulsom]

What does your pivot table look like? I don't see how that formula can return #N/A unless your pivot table contains it. You may need to convert A7 to textL

=SUMPRODUCT(($A$3:$A$4=$A7&"")*($B$1:$E$1=B$6)*($B$3:$E$4))

 

[gomukuma]

My Pivot table is as the data is set out above (well there's a couple of extra rows but i adjusted for that.)

Extract: ( I didn't realise this would work before) Data starts on C5 and sheet is called value

=SUMPRODUCT((Value!$C$5:$C$78=$A11&"")*(Value!$D$1:$AM$1="LTM")*(Value!$D$5:$AD$79)) I tried adding the extra above put it didnt seem to make a difference.


[TABLE="width: 867"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[TD]PYTM</SPAN>[/TD]
[/TR]
[TR]
[TD]Sum of Value[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]£42,245[/TD]
[TD="align: right"]£5,495[/TD]
[TD="align: right"]£18,849[/TD]
[TD="align: right"]£3,201[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£3,585[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£18,265[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£32,766[/TD]
[TD="align: right"]£22,461[/TD]
[TD="align: right"]£12,792[/TD]
[TD="align: right"]£83,607[/TD]
[TD="align: right"]£38,234[/TD]
[TD="align: right"]£89,612[/TD]
[TD="align: right"]£147,903[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£700[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]-£585[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]1147[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£3,340[/TD]
[TD="align: right"]£2,923[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£21,418[/TD]
[TD="align: right"]£8,325[/TD]
[TD="align: right"]£3,324[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£7,147[/TD]
[/TR]
[TR]
[TD]1148[/TD]
[TD="align: right"]£8,979[/TD]
[TD="align: right"]£200[/TD]
[TD="align: right"]£11,705[/TD]
[TD="align: right"]£9,408[/TD]
[TD="align: right"]£11,149[/TD]
[TD="align: right"]£7,189[/TD]
[TD="align: right"]£14,182[/TD]
[TD="align: right"]£16,050[/TD]
[TD="align: right"]£31,353[/TD]
[TD="align: right"]£13,106[/TD]
[TD="align: right"]£16,669[/TD]
[TD="align: right"]£13,682[/TD]
[/TR]
[TR]
[TD]1151[/TD]
[TD="align: right"]£559,356[/TD]
[TD="align: right"]£443,392[/TD]
[TD="align: right"]£407,815[/TD]
[TD="align: right"]£518,273[/TD]
[TD="align: right"]£541,990[/TD]
[TD="align: right"]£264,346[/TD]
[TD="align: right"]£599,805[/TD]
[TD="align: right"]£334,728[/TD]
[TD="align: right"]£296,725[/TD]
[TD="align: right"]£414,555[/TD]
[TD="align: right"]£231,124[/TD]
[TD="align: right"]£315,032[/TD]
[/TR]
[TR]
[TD]1152[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£2,956[/TD]
[TD="align: right"]£12,439[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£3,554[/TD]
[TD="align: right"]£5,923[/TD]
[TD="align: right"]£6,617[/TD]
[TD="align: right"]£8,852[/TD]
[TD="align: right"]£2,926[/TD]
[/TR]
[TR]
[TD]1153[/TD]
[TD="align: right"]£12,963[/TD]
[TD="align: right"]£823[/TD]
[TD="align: right"]£5,552[/TD]
[TD="align: right"]£19,331[/TD]
[TD="align: right"]£2,910[/TD]
[TD="align: right"]£20,660[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£4,969[/TD]
[TD="align: right"]£34,843[/TD]
[TD="align: right"]£23,559[/TD]
[/TR]
[TR]
[TD]1160[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£2,658[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£2,765[/TD]
[/TR]
[TR]
[TD]1162[/TD]
[TD="align: right"]£898[/TD]
[TD="align: right"]£1,725[/TD]
[TD="align: right"]£7,628[/TD]
[TD="align: right"]£2,358[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£3,640[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£480[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]1166[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[/TR]
[TR]
[TD]1171[/TD]
[TD="align: right"]£49,435[/TD]
[TD="align: right"]£10,641[/TD]
[TD="align: right"]£31,632[/TD]
[TD="align: right"]£71,357[/TD]
[TD="align: right"]£30,599[/TD]
[TD="align: right"]£121,090[/TD]
[TD="align: right"]£85,620[/TD]
[TD="align: right"]£7,658[/TD]
[TD="align: right"]£4,579[/TD]
[TD="align: right"]£2,518[/TD]
[TD="align: right"]£1,044[/TD]
[TD="align: right"]£40,725[/TD]
[/TR]
[TR]
[TD]1172[/TD]
[TD="align: right"]£114,607[/TD]
[TD="align: right"]£155,013[/TD]
[TD="align: right"]£611,829[/TD]
[TD="align: right"]£907,639[/TD]
[TD="align: right"]£811,822[/TD]
[TD="align: right"]£398,317[/TD]
[TD="align: right"]£546,746[/TD]
[TD="align: right"]£373,282[/TD]
[TD="align: right"]£149,558[/TD]
[TD="align: right"]£210,858[/TD]
[TD="align: right"]£168,745[/TD]
[TD="align: right"]£110,717[/TD]
[/TR]
[TR]
[TD]1173[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£0[/TD]
[TD="align: right"]£8,255[/TD]
[TD="align: right"]£48,938[/TD]
[TD="align: right"]£48,507[/TD]
[TD="align: right"]£12,392[/TD]
[TD="align: right"]£32,180[/TD]
[TD="align: right"]£35,323[/TD]
[TD="align: right"]£40,229[/TD]
[TD="align: right"]£36,843[/TD]
[TD="align: right"]£26,359[/TD]
[TD="align: right"]£38,654[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=11></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

You have a different number of columns here:

(Value!$D$1:$AM$1="LTM")*(Value!$D$5:$AD$79)

and rows here:

(Value!$C$5:$C$78=$A11&"") .... (Value!$D$5:$AD$79))

 

[gomukuma]

Sorry for the delay, ive just picked this up again.
Finally got it to work, made sure the no of columns and rows matched and that got me what i needed

Thanks very much for your help Andrew!