# summing arrays..

1. ## summing arrays..

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.

2. ## Re: summing arrays..

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.

3. ## Re: summing arrays..

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.

4. ## Re: summing arrays..

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!

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

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