# Thread: Any way to get a SUMPRODUCT formula for non-contiguous cells? Thanks:  3 Post #5249536 (1)Post #5249538 (1)Post #5249551 (1) Likes:  4 Post #5249556 (1)Post #5249536 (1)Post #5249538 (1)Post #5249551 (1)

1. ## Any way to get a SUMPRODUCT formula for non-contiguous cells?

What i would like is Sumproduct of array1= A1,A3. Array2 = B1, B3.

=SUMPRODUCT((A1,A3),(B1,B3)) gives #VALUE . If you try to use {}, SUMPRODUCT will only accept constant numbers, not cell references.

When i type the formula with the (), the formula tip seems to suggest it is working. For instance, when i type
"=SUMPRODUCT((A1,A3)," after typing the comma it highlights array2, suggesting to me it accepted the expression "(A1,A3)" as array1...yet still final result is #VALUE .

Is it possible?

Thanks!  Reply With Quote

2. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

No, I don't think you can use SUMPRODUCT on non-contiguous ranges).
But you could use a number of other formulas for your simple example, like:
=SUMPRODUCT(A1,B1)+SUMPRODUCT(A3,B3)
=SUM(A1*B1,A3*B3)
=(A1*B1)+(A3*B3)

I have a feeling that you are oversimplying the real problem. If you tell us what the real ranges look like, and if there is any sort of pattern, we may be able to provide alternate solutions for you.  Reply With Quote

3. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

Maybe something like this...

=SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))

M.  Reply With Quote

4. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

Maybe something like this...

=SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))
EDIT: I was getting unexpected results using your formula initially, but then I noticed that you are using C1 and C3, and not B1 and B3 as in the original question.
So if you were tailoring your response to his conditions, it should be:
Code:
`=SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},B1,B3))`  Reply With Quote

5. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells? Originally Posted by Joe4 Marcelo,
I tried that, and it seemed to double my expected result.

For example, if A1=2, B1=3, A3=3, B3=5, the expected result is (2*4) + (3*5) = 23.
But your formula is returning 46 for me.
Joe,

It worked for me - returned 23 as expected.

M.  Reply With Quote

6. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

Marcelo,
Go back and see the edit I made to my response a few minutes ago.
It looks like you were using different cell addresses than were posted in the original question, and I missed you changed that.
Just wanted to make sure that the OP sees that you did that, or they might have the same issue.  Reply With Quote

7. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

 A B C D E 1 2 4 Result 2 100 200 23 3 3 5

Formula in E2
=SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},C1,C3))

M.  Reply With Quote

8. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells? Originally Posted by Joe4 Marcelo,
Go back and see the edit I made to my response a few minutes ago.
It looks like you were using different cell addresses than were posted in the original question, and I missed you changed that.
Just wanted to make sure that the OP sees that you did that, or they might have the same issue.
Oh, by accident i used C1 and C3 in the second array rather than B1 and B3.
But the formula works.

M.  Reply With Quote

9. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

Oh, by accident i used C1 and C3 in the second array rather than B1 and B3.
But the formula works.
Yep, it works, after you account for that.
Sorry if that wasn't clear in my edit.  Reply With Quote

10. ## Re: Any way to get a SUMPRODUCT formula for non-contiguous cells?

Another way

=SUMPRODUCT(A1:A3,C1:C3, --(MOD(ROW(A1:A3),2)=1))

M.  Reply With Quote

## User Tag List

#### Tags for this Thread

#value, formula, sumproduct, type #### Posting Permissions

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