Array formula help

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

Have a 5 column data table, columns 1-3 string values, columns 4-5 numerical values and each row is a unique record.

e.g.
a, b, c, 100, 4
d, e, f, 200, 20

On another sheet, I use two SUMIFS formula's to return values from column 4 and column 5 into (e.g.) columns A and B

(1)
Rich (BB code):
=SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,N1,Data!$N$5:$N$146,O1,Data!$O$5:$O$146,P1)

(2)
Rich (BB code):
=SUMIFS(Data!$Q$5:$Q$146,Data!$M$5:$M$146,N1,Data!$N$5:$N$146,O1,Data!$O$5:$O$146,P1)

I'd like a formula like:=[SUM(1)-SUM(2)]/SUM(1), without helper columns or showing formula (2) results anywhere (no hidden column)

I tried an array formula but result is not correct:
Rich (BB code):
={(SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4)-SUMIFS(Data!$Q$5:$Q$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4))/SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4)}

Can someone suggest suitable formula?

TIA,
Jack
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
VBA is needed for jagged arrays, Excel can only return one level from the worksheet. A pivot table calculated field is better than formulas here:


Excel 2010
ABCDEFGHIJKLM
1Group1Group2Group3Series1Series2
2ELOTDCKQN10087QFBKKMEAM0.547038328
3AWUAFKGRD2032
4LXHRFHTGH1377Group1Group2Group3Sum of Series1Sum of Series2Sum of Field1
5SDYSKXLTX999QFBKKMEAM2871300.547038328
6GJBCNBH118
7QFBKKMEAM7452
8BAYLYKKUX9252
9KVFOMNEJY3323
10FZDTPRKSS479
11QFBKKMEAM5317
12DQLTQRGXG9115
13LDMQMJSC9333
14HCFEIFHJA2128
15GCHMGTCQI531
16KJBCFENJC7645
17QFBKKMEAM706
18GXCUZWEVC4843
19KIQNWFFHW997
20JFPEDNFTT1558
21RNSITLQHP3758
22QFBKKMEAM8612
23NHJFLLEX3291
24QFBKKMEAM443
Sheet9
Cell Formulas
RangeFormula
K2=(SUMIFS($D$2:$D$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2)-SUMIFS($E$2:$E$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2))/SUMIFS($D$2:$D$24,$A$2:$A$24,H2,$B$2:$B$24,I2,$C$2:$C$24,J2)
 
Upvote 0
Hi, thanks for the suggestion, however, each triplet (Group 1 & Group 2 & Group 3) is unique in the LHS table (in your example cols A:E, i.e. you wouldn't have row 17 and row 11)

There is a single data table with unique rows.

There is an input sheet where 3 variables (per row) that match against the data table and returns a value
Duplicate rows in the input sheet can exist, but must return the unique value from the data table that the 3 variables evaluate to.

Each triplet of variables returns 2 values, I want to sum each column of returned value and calculate the percentage totals of the two columns.

Triplet1 = 100 and 50
Triplet2 = 200 and 40
Triplet3 = 100 and 50

Output required (400 + 140) / 400

The suggested formula looks similar to what I tried, except for wanting a range of criteria, rather than single cell (e.g. H2 vs H13:H17):
Code:
=(SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,Input!D13:D17,Data!$N$5:$N$146,Input!E13:E17,Data!$O$5:$O$146,Input!F13:F17)-

SUMIFS(Data!$Q$5:$Q$146,Data!$M$5:$M$146,Input!D13:D17,Data!$N$5:$N$146,Input!E13:E17,Data!$O$5:$O$146,Input!F13:F17))/

SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,Input!D13:D17,Data!$N$5:$N$146,Input!E13:E17,Data!$O$5:$O$146,Input!F13:F17)
But output is not correct
 
Last edited:
Upvote 0
I'm not sure sumifs/countifs can accept array criteria, but here's what did:


Excel 2010
ABCDEFGHIJ
1Group1Group2Group3Series1Series2
2SilverLeftOxygen10087
3VioletEastNeon2032RedLeftLithium
4RedWestBeryllium1377OrangeUpCarbon
5IndigoWestLithium999VioletWestBeryllium
6BlackSouthCarbon118SilverEastFluorine
7RedThereFluorine9252
8VioletSouthCarbon3323
9YellowHereCarbon479
10RedRightNeon5317
11WhiteHereHelium9115
12VioletThereOxygen9333
13VioletHereOxygen2128
14VioletUpLithium531
15RedThereNeon7645-0.18182
16OrangeNorthNeon4843
17GreenRightCarbon997-0.18182
18WhiteNorthNeon1558
19BlueNorthFluorine3758
20YellowDownBoron3291
Sheet9 (3)
Cell Formulas
RangeFormula
I15=((D4+D14)-(E4+E14))/(D4+D14)
I17=(SUMPRODUCT($D$2:$D$20,COUNTIF($H$3:$H$6,$A$2:$A$20),COUNTIF($I$3:$I$6,$B$2:$B$20),COUNTIF($J$3:$J$6,$C$2:$C$20))-SUMPRODUCT($E$2:$E$20,COUNTIF($H$3:$H$6,$A$2:$A$20),COUNTIF($I$3:$I$6,$B$2:$B$20),COUNTIF($J$3:$J$6,$C$2:$C$20)))/SUMPRODUCT($D$2:$D$20,COUNTIF($H$3:$H$6,$A$2:$A$20),COUNTIF($I$3:$I$6,$B$2:$B$20),COUNTIF($J$3:$J$6,$C$2:$C$20))


Even if there is a shorter SUMIFS formula I prefer helper columns, VBA, or a pivot table with VBA here.
 
Upvote 0
I can't use a helper column or VBA or pivot in this instance, that's the conditions placed by the task masters!

Worse comes worse, I will use a helper column but it's very sensitive data which they want the user to not see so trying to find non VBA/non Pivot solution.

I'll check the SUMPRODUCT suggestion shortly, thanks for helping and continuing to @sheetspread :)
 
Upvote 0
Are you wanting 4 sumifs formulas added together? eg in your first sumifs you want criteria S1,T1,U1 then S2,T2,U2 etc? If so use sumproduct(sumifs(etc.
 
Upvote 0
According to Dave Bruns you can include up to 2 criteria (the last has to be a row/column pair). The above example requires 3:


Excel 2010
ABCDEFGHIJ
1Group1Group2Group3Series1Series2
2SilverLeftOxygen10087Group1Group2Group3
3VioletEastNeon2032RedLeftLithium
4RedWestBeryllium1377OrangeUpCarbon
5IndigoWestLithium999VioletWestBeryllium
6BlackSouthCarbon118SilverEastFluorine
7RedThereFluorine9252
8VioletSouthCarbon3323
9SilverHereCarbon479186
10RedRightNeon5317
11WhiteHereHelium9115186
12VioletThereOxygen9333
13VioletHereOxygen2128
14VioletUpLithium531
15RedThereNeon7645
16OrangeNorthNeon4843
17GreenRightCarbon997
18WhiteNorthNeon1558
19BlueNorthFluorine3758
20YellowDownBoron3291
Sheet9 (4)
Cell Formulas
RangeFormula
H9=D2+D3+D4+D14
H11{=SUM(SUMIFS(D2:D20,A2:A20,H3:H6,B2:B20,TRANSPOSE(I3:I6)))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Morning, I ended up using a helper column in the lookup data to create distinct keys and using VLOOKUP to retrieve required data.

Array formula that now works ($A$1 contains last used row number of sheet, within INDIRECT):
Rich (BB code):
{=IFERROR((

SUMPRODUCT(SUMIFS(Data!$R$5:$R$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16)-

SUMPRODUCT(SUMIFS(Data!$S$5:$S$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16))/

SUMPRODUCT(SUMIFS(Data!$R$5:$R$146,Data!$O$5:$O$146,INDIRECT("$D$14:$D$"&$A$1),Data!$P$5:$P$146,INDIRECT("$E$14:$E$"&$A$1),Data!$Q$5:$Q$146,INDIRECT("$F$14:$F$"&$A$1)),L14:L16), "")}
Using spacing to show it's still (A-B)/A

I also discovered applying OFFSET to a named ranges (2D table) to isolate a single column returns #VALUE if used within an array formula, so this would error:
Rich (BB code):
{=IFERROR((SUMPRODUCT(SUMIFS(OFFSET(Table_Rates,,5,1),OFFSET(Table_Rates,,2,1),INDIRECT("$D$14:$D$"&$A$1),OFFSET(Table_Rates,,3,1),INDIRECT("$E$14:$E$"&$A$1),OFFSET(Table_Rates,,4,1),INDIRECT("$F$14:$F$"&$A$1)),L14:L16)... etc}
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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