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

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.

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.

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

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.

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.

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.

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.

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.

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.