Sumproduct with different array sizes

jkj115

New Member
Joined
Dec 16, 2011
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have two sets of data. I want to multiply the data in one set by the data in the other if the store name matches. My issue is that one set of data will be missing some of the stores from the first set. I want to be able to calculate the total without calculating the individual items.

This will make more sense in the attachment. I am thinking maybe an array formula but I really don't know how to work with those that well.

Different Size Arrays.xlsx
DEFGHIJKLMNOP
3Units Sold
4
5StoreMonth 1Month 2Month 3Month 4Month 5
6Store 148385
7Store 245843
8Store 31041074
9Store 4911059
10Store 535875
11Store 677252
12Store 713660
13Store 824542
14Store 915522
15Store 107910710
16
17Price per Unit
18StoreMonth 1Month 2Month 3Month 4Month 5
19Store 1$ 23.00$ 27.00$ 27.00$ 28.00$ 26.00
20Store 2$ 22.00$ 36.00$ 32.00$ 23.00$ 37.00
21Store 3$ 40.00$ 40.00$ 36.00$ 25.00$ 33.00
22Store 5$ 23.00$ 27.00$ 34.00$ 39.00$ 21.00
23Store 6$ 39.00$ 24.00$ 22.00$ 25.00$ 34.00
24Store 7$ 34.00$ 38.00$ 22.00$ 32.00$ 34.00
25Store 9$ 30.00$ 28.00$ 34.00$ 22.00$ 32.00
26Store 10$ 33.00$ 23.00$ 33.00$ 23.00$ 26.00
27
28
29Intended Result
30StoreMonth 1Month 2Month 3Month 4Month 5
31Store 1$ 92.00$ 216.00$ 81.00$ 224.00$ 130.00
32Store 2$ 88.00$ 180.00$ 256.00$ 92.00$ 111.00
33Store 3$ 400.00$ 160.00$ 360.00$ 175.00$ 132.00
34Store 4#N/A#N/A#N/A#N/A#N/A
35Store 5$ 69.00$ 135.00$ 272.00$ 273.00$ 105.00
36Store 6$ 273.00$ 168.00$ 44.00$ 125.00$ 68.00
37Store 7$ 34.00$ 114.00$ 132.00$ 192.00$ -
38Store 8#N/A#N/A#N/A#N/A#N/A
39Store 9$ 30.00$ 140.00$ 170.00$ 44.00$ 64.00
40Store 10$ 231.00$ 207.00$ 330.00$ 161.00$ 260.00
41
42Total$ 1,217.00$ 1,320.00$ 1,645.00$ 1,286.00$ 870.00<need to get this without calculating individual store data
Sheet1
Cell Formulas
RangeFormula
E31:I40E31=VLOOKUP($D31,$D$6:$I$15,COLUMNS($D$6:E$15),FALSE)*VLOOKUP($D31,$D$19:$I$26,COLUMNS($D$19:E$26),FALSE)
E42:I42E42=SUM(E31:E33,E35:E37,E39:E40)
 
I'm assuming you want a SUMIF of only the 2nd table using the first table as a filter:
MrExcel_20240104.xlsx
ABCDEF
1Units Sold
2
3StoreMonth 1Month 2Month 3Month 4Month 5
4Store 148385
5Store 245843
6Store 31041074
7Store 4911059
8Store 535875
9Store 677252
10Store 713660
11Store 824542
12Store 915522
13Store 107910710
14
15Price per Unit
16StoreMonth 1Month 2Month 3Month 4Month 5
17Store 12327272826
18Store 22236322337
19Store 34040362533
20Store 52327343921
21Store 63924222534
22Store 73438223234
23Store 93028342232
24Store 103323332326
25
26
27Intended Result
28StoreMonth 1Month 2Month 3Month 4Month 5
29Total1217132016451286870
30Sum 2nd Question1127415714726
Sheet2
Cell Formulas
RangeFormula
B29:F29B29=SUM(BYROW($A$4:$A$13,LAMBDA(s,XLOOKUP(s,$A$17:$A$25,B$17:B$25,0,0)))*B$4:B$13)
B30:F30B30=SUM(BYROW(FILTER($A$4:$A$13,B$4:B$13>5),LAMBDA(r,SUMIF($A$17:$A$24,r,B$17:B$24))))
yep this was it thanks so much!
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
You’re welcome…glad we could help and welcome to the MrExcel board! Please see Fluff’s request to update your account details with your Excel version. Knowing that up front let’s others know what function set is available to you, which often influences the approach taken. You can see an example of this when examining my first offering using pre-365 functions and the excellent offering from @felixstraube using 365 functions.
 
Upvote 0
Sorry one follow up (I have been trying to solve this on my own but am stuck). If all the data lived in one table, is there a way to add additional filters to recognize the qualifier column? see below. So trying to sum the sales for stores with more than 5 units.


Different Size Arrays.xlsx
ABCDEFGHIJ
1
2
3StoreQualifierMonth 1Month 2Month 3
4Store 1Units483
5Store 2Units458
6Store 3Units10410
7Store 4Units9110
8Store 5Units358
9Store 6Units772
10Store 7Units136
11Store 8Units245
12Store 9Units155
13Store 10Units7910
14Store 1Sales$ 23.00$ 27.00$ 27.00
15Store 2Sales$ 22.00$ 36.00$ 32.00
16Store 3Sales$ 40.00$ 40.00$ 36.00
17Store 5Sales$ 23.00$ 27.00$ 34.00
18Store 6Sales$ 39.00$ 24.00$ 22.00
19Store 7Sales$ 34.00$ 38.00$ 22.00
20Store 9Sales$ 30.00$ 28.00$ 34.00
21Store 10Sales$ 33.00$ 23.00$ 33.00
22
23<sum the total sales for stores with units >5
Sheet1
 
Upvote 0
There are several approaches for a consolidated range...here is one:
MrExcel_20240104.xlsx
ABCDE
3StoreQualifierMonth 1Month 2Month 3
4Store 1Units483
5Store 2Units458
6Store 3Units10410
7Store 4Units9110
8Store 5Units358
9Store 6Units772
10Store 7Units136
11Store 8Units245
12Store 9Units155
13Store 10Units7910
14Store 1Sales232727
15Store 2Sales223632
16Store 3Sales404036
17Store 5Sales232734
18Store 6Sales392422
19Store 7Sales343822
20Store 9Sales302834
21Store 10Sales332333
22
23Total Sales | U>511274157
Sheet3
Cell Formulas
RangeFormula
C23:E23C23=LET(data,$A$4:$E$21,threshold,5,store,CHOOSECOLS(data,1),qual,CHOOSECOLS(data,2),dcol,CHOOSECOLS(data,COLUMN()-COLUMN($A$3)+1),fs,IF((qual="Units")*(dcol>threshold),store),ffs,FILTER(fs,fs<>FALSE),SUM(ISNUMBER(MATCH(store,ffs,0))*(qual="Sales")*dcol))
 
Upvote 0
There are several approaches for a consolidated range...here is one:
MrExcel_20240104.xlsx
ABCDE
3StoreQualifierMonth 1Month 2Month 3
4Store 1Units483
5Store 2Units458
6Store 3Units10410
7Store 4Units9110
8Store 5Units358
9Store 6Units772
10Store 7Units136
11Store 8Units245
12Store 9Units155
13Store 10Units7910
14Store 1Sales232727
15Store 2Sales223632
16Store 3Sales404036
17Store 5Sales232734
18Store 6Sales392422
19Store 7Sales343822
20Store 9Sales302834
21Store 10Sales332333
22
23Total Sales | U>511274157
Sheet3
Cell Formulas
RangeFormula
C23:E23C23=LET(data,$A$4:$E$21,threshold,5,store,CHOOSECOLS(data,1),qual,CHOOSECOLS(data,2),dcol,CHOOSECOLS(data,COLUMN()-COLUMN($A$3)+1),fs,IF((qual="Units")*(dcol>threshold),store),ffs,FILTER(fs,fs<>FALSE),SUM(ISNUMBER(MATCH(store,ffs,0))*(qual="Sales")*dcol))
I updated my profile so thanks for that tip. This worked for me, but I ran into one more issue. In my data, I have some non-numeric values which causes a #value in this formula. Is there a way to adapt the formula so it can still work (rather than edit the raw data itself)?
 
Upvote 0
You could modify the formula to clean up the original source data. Here I've assigned the original data to a variable called dataorig and then the first two columns are temporarily removed to create an array consisting of only the rightmost mostly numeric values (an array called dor...data original rightmost). That mostly numeric array is processed by an IF statement to either replace non-numeric values with 0 or keep the original numeric values. The conditioned array is then recombined with the original two columns and the resultant array is then assigned to a variable called "data" (which looks more like what was originally assumed, in that only numeric values were expected in the rightmost columns). I think this should work for your purposes...but be aware of what is happening in the formula: a text value will test as TRUE when it is subjected to the logical test IF(text>5...and this would cause any text in the rightmost data columns that are located in the upper "Units" section to be considered as valid, meaning the corresponding Store would be used to find its associated "Sales" numbers, but by swapping out text values for 0's first (as is done here with the source data transformation mentioned above), the logical test IF(0>5 evaluates to FALSE, so those Stores will be ignored. I'm assuming this is what you want.
MrExcel_20240104.xlsx
ABCDE
3StoreQualifierMonth 1Month 2Month 3
4Store 1Units483
5Store 2Units458
6Store 3Units10410
7Store 4Units9110
8Store 5Unitsother text58
9Store 6Units772
10Store 7Units136
11Store 8Units245
12Store 9Units155
13Store 10Units7910
14Store 1Sales232727
15Store 2Salesa word3632
16Store 3Sales404036
17Store 5Sales23some text34
18Store 6Sales392422
19Store 7Sales343822
20Store 9Sales302834
21Store 10Sales332333
22
23Total Sales | U>511274157
Sheet3
Cell Formulas
RangeFormula
C23:E23C23=LET(dataorig,$A$4:$E$21,threshold,5,dor,DROP(dataorig,,2),data,HSTACK(TAKE(dataorig,,2),IF(ISNUMBER(dor),dor,0)),store,CHOOSECOLS(data,1),qual,CHOOSECOLS(data,2),dcol,CHOOSECOLS(data,COLUMN()-COLUMN($A$3)+1),fs,IF((qual="Units")*(dcol>threshold),store),ffs,FILTER(fs,fs<>FALSE),SUM(ISNUMBER(MATCH(store,ffs,0))*(qual="Sales")*dcol))
 
Upvote 0
You are a wizard! And so I have one more question and I really appreciate your time. In the below example, I want to total the square footage (SF) that does not have an associated rent figure in the table. The nuance is that the tenant may not show up at all under the Rent, or it may have $0 some months and then have a rent in another. So the formula should total the SF if the tenant either does not show up at all, or has $0 for rent.


Different Size Arrays.xlsx
ABCDEFGH
1
2
3StoreQualifierMonth 1Month 2Month 3
4Tenant 1SF1,8377,1825,917
5Tenant 2SF6,1655,1593,247
6Tenant 3SF8,6152,2641,590
7Tenant 4SF3,0711,3126,123
8Tenant 5SF8,2826,7824,456
9Tenant 6SF2,5371,2323,598
10Tenant 7SF5,4313,7436,020
11Tenant 8SF5,3716,8678,920
12Tenant 9SF5,5885,4989,307
13Tenant 10SF5,8285,4683,575
14Tenant 1Rent$ 27,699$ 10,757$ 21,516
15Tenant 2Rent$ -$ -$ 21,516
16Tenant 4Rent$ 10,223$ 15,996$ 25,155
17Tenant 6Rent$ 25,118$ 16,406$ 11,430
18Tenant 7Rent$ 10,499$ 18,661$ 23,009
19Tenant 8Rent$ 12,516$ 25,737$ 24,948
20Tenant 9Rent$ 16,546$ 11,218$ 11,636
21Tenant 10Rent$ 10,987$ 15,860$ 16,499
22
23Leased SF with No Rent
2423,06214,2056,046<intended result
25
Sheet1
Cell Formulas
RangeFormula
C24:D24C24=+C5+C6+C8
E24E24=E6+E8
 
Upvote 0
I suppose that rather than trying to find which tenants are not there, you could instead find the Rented Square Footage for Tenants shown with "Rent" in the Qualifier column AND whose monthly column has a value greater than 0 (the threshold value)...so we can simply adapt the previous formula. Then we need to find the total Square Footage (the sum of a column where Qualifier is "SF"). The answer sought is the difference between those two values:
MrExcel_20240104.xlsx
ABCDEF
3TenantQualifierMonth 1Month 2Month 3
4Tenant 1SF183771825917
5Tenant 2SF616551593247
6Tenant 3SF861522641590
7Tenant 4SF307113126123
8Tenant 5SF828267824456
9Tenant 6SF253712323598
10Tenant 7SF543137436020
11Tenant 8SF537168678920
12Tenant 9SF558854989307
13Tenant 10SF582854683575
14Tenant 1Rent276991075721516
15Tenant 2Rent0021516
16Tenant 4Rent102231599625155
17Tenant 6Rent251181640611430
18Tenant 7Rent104991866123009
19Tenant 8Rent125162573724948
20Tenant 9Rent165461121811636
21Tenant 10Rent109871586016499
22
23Leased SF with No Rent23062142056046
2423062142056046<intended result
Sheet4
Cell Formulas
RangeFormula
C23:E23C23=LET(dataorig,$A$4:$E$21,threshold,0,dor,DROP(dataorig,,2),data,HSTACK(TAKE(dataorig,,2),IF(ISNUMBER(dor),dor,0)),tenant,CHOOSECOLS(data,1),qual,CHOOSECOLS(data,2),dcol,CHOOSECOLS(data,COLUMN()-COLUMN($A$3)+1),ft,IF((qual="Rent")*(dcol>threshold),tenant),fft,FILTER(ft,ft<>FALSE),SqFtRent,SUM(ISNUMBER(MATCH(tenant,fft,0))*(qual="SF")*dcol),TotSqFt,SUM((qual="SF")*dcol),TotSqFt-SqFtRent)
C24:D24C24=+C5+C6+C8
E24E24=E6+E8
 
Upvote 0
this makes sense to me. Will try it on my data and let you know. thanks!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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