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)

	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!

 

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

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

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=11></COLGROUP>

 

[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!