# Thread: Array formula help Thanks: 0 Likes:  6 Post #5306940 (1)Post #5306955 (1)Post #5306495 (1)Post #5306963 (1)Post #5306646 (1)

1. ## Array formula help

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)
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)
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:
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

2. ## Re: Array formula help

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

Worksheet Formulas
CellFormula
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)

3. ## Re: Array formula help

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

4. ## Re: Array formula help

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)

Worksheet Formulas
CellFormula
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.

5. ## Re: Array formula help

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

6. ## Re: Array formula help

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.

7. ## Re: Array formula help

Originally Posted by steve the fish
use sumproduct(sumifs(etc.
I thought sum/countifs crtieria might allow more than 1 array but it didn't seem to work here, I'll try again.

8. ## Re: Array formula help

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)

Worksheet Formulas
CellFormula
H9=D2+D3+D4+D14

Array Formulas
CellFormula
H11{=SUM(SUMIFS(D2:D20,A2:A20,H3:H6,B2:B20,TRANSPOSE(I3:I6)))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

9. ## Re: Array formula help

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):
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:
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}`

Nice