SUMPRODUCT question

[ndox123]

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

	A	B	C	D	E	F	G	H	I
1		May YTD	May YTD	May YTD	May YTD	May YTD	May YTG	May YTG	May YTG
2	Store	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
3	Store A	200	100	50	250	500	600	500	500
4	Store B	200	100	80	275	400	400	300	399
5	Store C	300	200	75	300	700	500	500	500
6	Store D	300	300	25	300	400	400	300	200

<tbody>
</tbody>

Table 2

	May YTD	May YTG
Store A
Store B
Store C

<tbody>
</tbody>

 

[theBardd]

Try this

=SUMPRODUCT(B3:I6*(B1:I1="May YTD")*(A3:A6="Store A"))

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >May YTD</td><td >May YTD</td><td >May YTD</td><td >May YTD</td><td >May YTD</td><td >May YTG</td><td >May YTG</td><td >May YTG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >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 style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Store A</td><td style="text-align:right; ">200</td><td style="text-align:right; ">100</td><td style="text-align:right; ">50</td><td style="text-align:right; ">250</td><td style="text-align:right; ">500</td><td style="text-align:right; ">600</td><td style="text-align:right; ">500</td><td style="text-align:right; ">500</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Store B</td><td style="text-align:right; ">200</td><td style="text-align:right; ">100</td><td style="text-align:right; ">80</td><td style="text-align:right; ">275</td><td style="text-align:right; ">400</td><td style="text-align:right; ">400</td><td style="text-align:right; ">300</td><td style="text-align:right; ">399</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Store C</td><td style="text-align:right; ">300</td><td style="text-align:right; ">200</td><td style="text-align:right; ">75</td><td style="text-align:right; ">300</td><td style="text-align:right; ">700</td><td style="text-align:right; ">500</td><td style="text-align:right; ">500</td><td style="text-align:right; ">500</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Store D</td><td style="text-align:right; ">300</td><td style="text-align:right; ">300</td><td style="text-align:right; ">25</td><td style="text-align:right; ">300</td><td style="text-align:right; ">400</td><td style="text-align:right; ">400</td><td style="text-align:right; ">300</td><td style="text-align:right; ">200</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >May YTD</td><td >May YTG</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Store A</td><td style="text-align:right; ">1100</td><td style="text-align:right; ">1600</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Store B</td><td style="text-align:right; ">1055</td><td style="text-align:right; ">1099</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >Store C</td><td style="text-align:right; ">1575</td><td style="text-align:right; ">1500</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B11</td><td >=SUMPRODUCT(($B$1:$I$1=B$10)*($A$3:$A$6=$A11)*($B$3:$I$6))</td></tr></table></td></tr></table>

 

[Fluff]

Your ranges are wrong, try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		May YTD	May YTD	May YTD	May YTD	May YTD	May YTG	May YTG	May YTG						May YTD	May YTG
2	Store	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug					Store A	1100	1600
3	Store A	200	100	50	250	500	600	500	500					Store B	1055	1099
4	Store B	200	100	80	275	400	400	300	399					Store C	1575	1500
5	Store C	300	200	75	300	700	500	500	500
6	Store D	300	300	25	300	400	400	300	200
Sheet2

Cell Formulas
Range	Formula
O2	=SUMPRODUCT(($B$1:$I$1=O$1)*($A$3:$A$6 =$N2),$B$3:$I$6)

 

[ndox123]

Thank you ALL!!! that worked like magic for me

 

[Fluff]

Glad we could help & thanks for the feedback