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

[TomCon]

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!

 

[Joe4]

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.

 

[Marcelo Branco]

Maybe something like this...

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

M.

 

[Joe4]

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:

[COLOR=#333333]=SUMPRODUCT(CHOOSE({1;2},A1,A3),CHOOSE({1;2},[/COLOR][COLOR=#ff0000][B]B[/B][/COLOR][COLOR=#333333]1,[/COLOR][B][COLOR=#ff0000]B[/COLOR][/B][COLOR=#333333]3))[/COLOR]

 

[Marcelo Branco]

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.

 

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

 

[Marcelo Branco]

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

2​

[/td][td][/td][td]

4​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td][/td][td]

200​

[/td][td][/td][td]

23​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

3​

[/td][td][/td][td]

5​

[/td][td][/td][td][/td][/tr]
[/table]

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

M.

 

[Marcelo Branco]

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.

 

[Joe4]

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.

 

[Marcelo Branco]

Another way

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

M.