Hi all,
I have a data in Table 1 looking like below and I've been trying to get the sum of all May YTD and May YTG amounts (to put in Table 2) for each store by using SUMPRODUCT but i keep getting #N/As.
The formula I came up with is (for example, i want to get the figure for Store A):
=SUMPRODUCT((B3:I6) * (B1:H1= "May YTD") * (A2:A6 = "Store A")). Where have I gone wrong? Appreciate your help!!!
Table 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTG[/TD]
[TD="align: center"]May YTG[/TD]
[TD="align: center"]May YTG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Store[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Store A[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]500
[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Store B[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD]275[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]300[/TD]
[TD]399[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Store C[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]75[/TD]
[TD]300[/TD]
[TD]700[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Store D[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]25[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]300[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]
Table 2 [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]May YTD[/TD]
[TD]May YTG[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a data in Table 1 looking like below and I've been trying to get the sum of all May YTD and May YTG amounts (to put in Table 2) for each store by using SUMPRODUCT but i keep getting #N/As.
The formula I came up with is (for example, i want to get the figure for Store A):
=SUMPRODUCT((B3:I6) * (B1:H1= "May YTD") * (A2:A6 = "Store A")). Where have I gone wrong? Appreciate your help!!!
Table 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTD[/TD]
[TD="align: center"]May YTG[/TD]
[TD="align: center"]May YTG[/TD]
[TD="align: center"]May YTG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Store[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Store A[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]250[/TD]
[TD]500
[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Store B[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD]275[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]300[/TD]
[TD]399[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Store C[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]75[/TD]
[TD]300[/TD]
[TD]700[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Store D[/TD]
[TD]300[/TD]
[TD]300[/TD]
[TD]25[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]300[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]
Table 2 [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]May YTD[/TD]
[TD]May YTG[/TD]
[/TR]
[TR]
[TD]Store A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]