+ Reply to Thread
Results 1 to 21 of 21

SUM/INDEX array formula

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    SUM/INDEX array formula

    Hi all,

    I need to get rid of a few helper columns and hence want to use an array formula instead (no way around it in this particular case).

    I am returning certain values from my "IA" sheet based on criteria on my "Range" sheet. In order to simplify things and reduce calculation times, I have already found the correct ROW for these criteria in my "IA ROW #" column with a MATCH formula.

    Now I want to use that column to calculate the SUM of all these cells in the "IA" sheet. Basically, I want to achieve the same result as in the green row but without the helper columns. Should be more clear in my sample file.
    Attached Files Attached Files

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: SUM/INDEX array formula

    Hello,

    sorry, but I'm not willing to download a large xlsb file. That's just too dangerous. Can you please create a sample file with less data, no macros and upload that? Happy to provide the principles of constructing a formula with your data structure, but I don't need all your data or your VBA to do that.

    cheers, teylyn

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    Sorry, understandable.

    See .xlsx file attached!
    Attached Files Attached Files
    Last edited by esbencito; 05-23-2018 at 02:52 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    VBA solution: results in row 6


    Please Login or Register  to view this content.
    Last edited by JohnTopley; 05-23-2018 at 08:57 AM.

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    Thanks for your VBA solution John! It seems to work, however, this is pretty painful to maintain and some clients tend to disable macros, so I was hoping that I could use an array formula instead? Am I completely off thinking that this is feasible??

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    I cannot think how you can do it with formulae, given the complexity of your helper columns to select the row numbers which in turn are the indices for column E.

    If it can be done it is way beyond my ability. What would possibly make it more feasible is having the logic which is used to define the row numbers in column A.

  7. #7
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    hmm... there would potentially be a way to simplify the complexity of the criteria to select the row numbers. Instead of having:

    Please Login or Register  to view this content.
    to define for which cells the IA data should be returned, the condition could just be
    Please Login or Register  to view this content.
    I noticed that I applied the above complex condition already somewhere else so I wouldn't need to do it again... would that make it more feasible?
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    In a "pseudo" formula, we want something like ....

    =SUMPRODUCT(--(D2:D600<>0)*(INDEX(IA!E2:E600,C2:C600)))


    Where the indices of E are in column C: I do not know how or if it can be done,

    How are the row numbers in column C derived ?

  9. #9
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    I'm using a MATCH formula to find the row considering several conditions. In order to speed up calculations and simplify things, I separated the INDEX and MATCH. Below is how the formula looks like in my original file:

    Please Login or Register  to view this content.
    I had an array formula before, but things were getting more and more complex as I kept adding criteria, so it stopped working and I decided to return the ROWs first with a helper column

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    I have not studied the formulae in detail but it is (becoming) obvious that is (probably) impossible to provide a formula solution without your helper columns.

    I assume you copy/paste the rows in C from another file. (?)

    I do understand about using VBA but there are times when it is most practical solution (although I have no issue with the use of helper columns).

  11. #11
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    Quote Originally Posted by JohnTopley View Post
    I have not studied the formula in detail but it is (becoming) obvious that is (probably) impossible to provide a formula solution without your helper columns.

    I assume you copy/paste the rows in C from another file. (?)
    Yes! I get the correct rows from somewhere else. The problem with helper columns is, that I would end up with 180 extra columns...

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    You have 16000+ columnsso why worry about 180 if it makes life easier.

    As you noted, the complex formula had a major impact on performance and any formulaic solution is going to have this impact.

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUM/INDEX array formula

    If you can add a new column (say column J) to the IA sheet that returns a row number (i.e. using =ROW()) then you can just use
    =SUM(SUMIFS(IA!E:E,IA!$J:$J,IFERROR((D$12:D$875<>0)*($C$12:$C$875),1E+100)))
    on your summary sheet (array-entered).
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  14. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUM/INDEX array formula

    Hi,

    Using the file you attached in post #3, array formula** in D5:

    =SUM(INDEX(IA!E:E,N(IF(1,IF(D12:D876<>" - ",IF(D12:D876<>0,IF(ISNUMBER($C12:$C876),$C12:$C876)))))))

    Copy across as required.

    Regard
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: SUM/INDEX array formula

    Wrong again .....!!!! Another lesson for me!!!
    Last edited by JohnTopley; 05-24-2018 at 09:17 AM.

  16. #16
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    Thanks guys...!!! Both solutions are incredible! Fast, simple, easy to maintain and returning the required results!! Just what I needed, super helpful! The world needs more people like you

    @John - There's nothing these guys cannot do!

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUM/INDEX array formula

    You're welcome. I'll assume you didn't intend to give me negative rep then?

  18. #18
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    huh? what you mean?

  19. #19
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUM/INDEX array formula

    I think you clicked the wrong optionbutton when giving me rep. No big deal- it's quite easy to do when adding a comment.

  20. #20
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: SUM/INDEX array formula

    Sorry! That obviously wasn't intentionally. Gave you an additional rep as presumably I cannot undo the previous one! Thanks again!!

  21. #21
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: SUM/INDEX array formula

    Thank you. It wasn't a problem- just wanted to let you know that you need to be a little careful with that dialog.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  2. Replies: 5
    Last Post: 12-02-2015, 05:23 AM
  3. Index & Array Formula
    By Tim204030 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2015, 11:47 AM
  4. [SOLVED] Help with Array and index Formula
    By shayej in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-26-2015, 06:22 PM
  5. Sum with array formula in index
    By Matthys.Steyn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2013, 10:44 AM
  6. Replies: 2
    Last Post: 06-20-2012, 12:22 PM
  7. Index array formula?
    By kdestef1 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-11-2009, 10:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1