SUMPRODUCT question

N

ndox123

New Member
Joined
Aug 1, 2019
Messages
6
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]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
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>
 
Upvote 0
Your ranges are wrong, try

Book1
ABCDEFGHIJKLMNOP
1May YTDMay YTDMay YTDMay YTDMay YTDMay YTGMay YTGMay YTGMay YTDMay YTG
2StoreJanFebMarAprMayJunJulAugStore A11001600
3Store A20010050250500600500500Store B10551099
4Store B20010080275400400300399Store C15751500
5Store C30020075300700500500500
6Store D30030025300400400300200
Sheet2
Cell Formulas
RangeFormula
O2=SUMPRODUCT(($B$1:$I$1=O$1)*($A$3:$A$6 =$N2),$B$3:$I$6)
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
73
Cubist
Cubist
S
  • Solved
Latest Data pull-up from the corresponding date
Replies
8
Views
207
RobP
R
T
  • Question Question
Retrieve info with different date formats
2
Replies
11
Views
261
tigerzen
T
A
  • Question Question
Negative values sum
Replies
4
Views
461
Scott Huish
Scott Huish
S
  • Question Question
Latest Data Pull-up from the corresponding date-Single party
Replies
3
Views
128
Sergius
S

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top