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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
373
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion 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.
 
Upvote 0
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:
[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]
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top