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.

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??

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.

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?

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 ?

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.

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

Originally Posted by JohnTopley

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...

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.

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!

huh? what you mean?

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