Array formula help
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Array formula help

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,100
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default 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
    Last edited by JackDanIce; Jul 9th, 2019 at 08:35 AM.


  4. #4
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,100
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,100
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula help

    Quote Originally Posted by steve the fish View Post
    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.
    Last edited by sheetspread; Jul 9th, 2019 at 07:34 PM.

  8. #8
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,100
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default 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}
    Last edited by JackDanIce; Jul 10th, 2019 at 05:16 AM.


  10. #10
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,100
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array formula help

    Nice


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •