summing arrays..

[Alexander_Golinsky]

Heya! Again problem with arrays This question is rather theoretical, and, please, dont solve it other way - what is interesting is that exact point

When i try to sum an array which is the result of either index or if functions then excel just ignore sum function. so 2 question:

1. Why?
2. How to overcome this without changing all these index and if functions.

The example of the problem is in the attachment. Thanks and regards in advance.

[tigeravatar]

Not really a need for an array formula, but since you specifically requested using an array formula, this will work:

=SUM(IF(A1:D1="d",A3:D3))

The reason the other one isn't, is because is only returning the number from a specific column, and no other numbers. You need to eliminate that condition.

[JBeaucaire]

Because the structure is incorrect for that. Ultimately you have backended you way into a SUM(IF) situation, for which there is a specific correct syntax, and you're not using it. Just growing a monster formula forever like that isn't all that theoretically useful, my conclusion.

The array format for your scenario is pretty simple:

=SUM(IF($A$1:$D$1="d", $A$3:$D$3)) (entered as an array)


And of course, Excel already has a non-array SUMIF function for this very purpose:

=SUMIF($A$1:$D$1, "d", $A$3:$D$3)


And there is even a SUMIFS() function if you need more than one criteria.

[Alexander_Golinsky]

You're two guys just brilliant. I mean, simplicity is what rules and is really beautiful, and i need, probably, to reset my mind. Thank you for the lesson!