Sum Product Query on mass amount of Data, 3 variables

H

horkstar12

New Member
Joined
May 16, 2016
Messages
9
Good morning,

Looking for a little bit of help on a sum product I have messing with my head!

What i have is a mass expanse of data, 4380 lines long A-AZ wide, I need my Sumproduct to look over 3 conditions, Type, Wash and then week.

Each cell will show me the total monies for that week, in that product type (jeans) in that colour.

The below is setup in the Data Tab:
- In Column B i have product type, i.e. jeans,
- In K i have Colour i.e. black, blue..etc
- In row 5, column L through to AK are the week numbers i.e. Week 1, Week 2 etc
- The data set is in L6 through to AK4370

The current formula i have setup is:

=SUMPRODUCT(($A$2=Data!$B$6:$B$4370)*(Overview!B$2=Data!$L$5:$AK$5)*(Overview!$A3=Data!$K$6:$K$4370),Data!$L$6:$AK$4370)

Overview is where the table will be setup:

JeansWeek 1Week 2Week 3
GREY
BLUE
BLACK

<tbody>
</tbody>

<tbody>
</tbody>

Also, as a side note, can you add arrays after the data set or does it need to be before?

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
productcolourdatewknumjeans
jeansblack01/01/20161weeknum>>>1234567891011
jeansblue03/01/20161black12111121110
hatsblack05/01/20161blue20211111210
jeansblue07/01/20161
jeansblack09/01/20162
hatsblue11/01/20162
jeansblack13/01/20162
jeansblue15/01/20163
hatsblue17/01/20163
jeansblack19/01/20163
jeansblue21/01/20163
hatsblack23/01/20164
jeansblue25/01/20164this seems easy so I guess there is more to it…
jeansblack27/01/20164
hatsblue29/01/20165
jeansblack31/01/20165
jeansblue02/02/20165
hatsblue04/02/20165
jeansblack06/02/20166
jeansblue08/02/20166
hatsblack10/02/20166
jeansblue12/02/20167
jeansblack14/02/20167
hatsblue16/02/20167
jeansblack18/02/20167
jeansblue20/02/20168
hatsblue22/02/20168
jeansblack24/02/20168
jeansblue26/02/20169
hatsblack28/02/20169
jeansblue01/03/20169
jeansblack03/03/20169
hatsblue05/03/201610
jeansblack07/03/201610
jeansblue09/03/201610
hatsblue11/03/201611

<colgroup><col span="2"><col><col span="2"><col><col span="12"></colgroup><tbody>
</tbody>
 
Upvote 0
oldbrewer said:
productcolourdatewknumjeans
jeansblack01/01/20161weeknum>>>1234567891011
jeansblue03/01/20161black12111121110
hatsblack05/01/20161blue20211111210
jeansblue07/01/20161
jeansblack09/01/20162
hatsblue11/01/20162
jeansblack13/01/20162
jeansblue15/01/20163
hatsblue17/01/20163
jeansblack19/01/20163
jeansblue21/01/20163
hatsblack23/01/20164
jeansblue25/01/20164this seems easy so I guess there is more to it…
jeansblack27/01/20164
hatsblue29/01/20165
jeansblack31/01/20165
jeansblue02/02/20165
hatsblue04/02/20165
jeansblack06/02/20166
jeansblue08/02/20166
hatsblack10/02/20166
jeansblue12/02/20167
jeansblack14/02/20167
hatsblue16/02/20167
jeansblack18/02/20167
jeansblue20/02/20168
hatsblue22/02/20168
jeansblack24/02/20168
jeansblue26/02/20169
hatsblack28/02/20169
jeansblue01/03/20169
jeansblack03/03/20169
hatsblue05/03/201610
jeansblack07/03/201610
jeansblue09/03/201610
hatsblue11/03/201611

<tbody>
</tbody>
Click to expand...

Hi,

Thanks for the reply,

My current Sumproduct brings through NA so im unsure what exactly is wrong as i believe i had balanced out the arrays?

sorry im new to Sumproducts so help is appreciated

Thanks
 
Upvote 0
allcols have to be same length eg A1:A100 D1:D100

copy 5 rows and paste on here with the formula you are using
 
Upvote 0
Thanks, this is the table, weeks go upto week 26:

JEANSWEEK 1 £WEEK 2 £WEEK 3 £WEEK 4 £WEEK 5 £WEEK 6 £WEEK 7 £WEEK 8 £
BLACK#N/A
BLEACH WASH
BLUE WASH
INDIGO WASH
GREY WASH
WHITE
COLOUR
PRINT

<colgroup><col><col span="8"></colgroup><tbody>
</tbody>

This is the formula:
=SUMPRODUCT((A1='[SS16 DEMAND WEEK26.xls]SS16 Cat Level £'!$B$6:$B$4605)*(A2='[SS16 DEMAND WEEK26.xls]SS16 Cat Level £'!$AS$6:$AS$4605)*(B1='[SS16 DEMAND WEEK26.xls]SS16 Cat Level £'!$K$5:$AJ$5),'[SS16 DEMAND WEEK26.xls]SS16 Cat Level £'!$K$6:$AJ$4605)


This is a copy of the data:

RangeProduct Line CodeProduct Line Description (Code)Catalogue NumberProduct DescriptionProduct ColourLINE COLCONCATWEEK 1 £WEEK 2 £WEEK 3 £WEEK 4 £WEEK 5 £WEEK 6 £WEEK 7 £WEEK 8 £WEEK 9 £WEEK 10 £WEEK 11 £WEEK 12 £WEEK 13 £WEEK 14 £WEEK 15 £WEEK 16 £WEEK 17 £WEEK 18 £WEEK 19 £WEEK 20 £WEEK 21 £WEEK 22 £WEEK 23 £WEEK 24 £WEEK 25 £WEEK 26 £CUM £PRODUCT ATTHOLIDAYNEW / RI DEPTEnd UseShapeCore / FashionWash
JEANSAB123LSKINNY JEANSAB123SKINNY JEANSBLACKAB123LBLACKAB123BLACK2,1763,4883,4463,1793,1363,5012,1252,4562,5221,2781,9312,7502,7822,3983,0843,0203,1152,2352,0992,0062,0953,7774,8533,2232,4902,50971,6700.000.00RIJEANSCASUALSKINNYCOREBLACK
JEANSAB123LSKINNY JEANSAB123SKINNY JEANSBLEACHWASHAB123LBLEACHWASHAB123BLEACHWASH1891952062912942261512957314676359304656817876707411,2836141,2401,0538271,00489185147216,1850.000.00RIJEANSCASUALSKINNYCOREBLEACH WASH
JEANSAB123LSKINNY JEANSAB123SKINNY JEANSBLUEAB123LBLUEAB123BLUE1,2561,1541,0911,6231,7122,0691,8721,6211,7449271,2571,2121,1141,1481,3891,1851,5221,4961,4671,4971,3611,6441,4521,3441,1731,21136,5400.000.00RIJEANSCASUALSKINNYCOREBLUE WASH
JEANSAB123LSKINNY JEANSAB123SKINNY JEANSINDIGOAB123LINDIGOAB123INDIGO1,2501,9071,6771,4841,6441,7482,2232,3552,8558021,9622,1011,6471,5292,3382,0102,3222,1702,0391,3491,5482,1702,6991,6741,5951,51048,6070.000.00RIJEANSCASUALSKINNYCOREINDIGO WASH



<colgroup><col><col><col><col><col><col><col><col><col span="35"></colgroup><tbody>
</tbody>
Apologies its so big but i wanted to show you the full data set.

The Sumproduct is looking up RANGE in column B, WASH in column AS and the week numbers in ROW 5 (K:AJ), the data set is then from K6:AJ4605

Really appreciate the help!
 
Upvote 0
=sumproduct((a1=9)*........does not work imho

=sumproduct(a1:a10=9)*(b1:b10)) is the basic format
 
Upvote 0
RangeProduct ColourWEEK 1 £WEEK 2 £WEEK 3 £WEEK 4 £WEEK 5 £week extracted
JEANSBLACK2,1763,4883,4463,1793,1363446######
JEANSBLEACHWASH189195206291294206
JEANSBLUE1,2561,1541,0911,6231,7121091
JEANSINDIGO1,2501,9071,6771,4841,6441677
slippersBLACK455259667359
slippersBLEACHWASH475461687561
glovesBLUE495663707763
glovesINDIGO515865727965
JEANSBLACK2,1763,4883,4463,1793,1363446
JEANSBLEACHWASH189195206291294206
col C
JEANSrow 17
BLUE
C19 > > > > > >WEEK 3 £
1091^^^^^^^^^
#########
=OFFSET($A$1,ROW()-1,MATCH($C$19,$C$1:$G$1,0)+1)
^^^^^^^^^
=SUMPRODUCT(($A$2:$A$11=$C$17)*($B$2:$B$11=$C$18)*($H$2:$H$11))
if you wanted week 4 total change C19 to week 4 £ and col H would extract week 4

<colgroup><col><col><col span="5"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
159
Kaleo
K
K
  • Solved
SumProduct help Multiple String Criteria
2
Replies
10
Views
846
Kemidan2014
K
C
  • Question
Sum Data Across Columns and Rows between Dates
2
Replies
19
Views
337
awoohaw
awoohaw
E
  • Question
Alternative to complex SumProduct to rank data by specific variable
Replies
3
Views
577
Peter_SSs
Peter_SSs
M
  • Question
Auto-populate data from multiple sheets into one master sheet- Help
Replies
1
Views
134
alansidman
A

Forum statistics

Threads
1,215,751
Messages
6,126,668
Members
449,326
Latest member
asp123

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