# SUMPRODUCT with (space) Intersection operator

#### jackms

##### New Member
I have a range name that consists of non-contiguous columns.

I can use the (space) Intersection operator with functions like SUM and AVERAGE , but get #VALUE when I try it with SUMPRODUCT.

Example: if range myrange consists of A:A,C:C

=SUM(myrange 3:3)=26
But =SUMPRODUCT(myrange 3:3,myrange 6:6)=#VALUE!

Note it works fine when myrange is contiguous (e.g. if myrange would consist of A:B, then the above SUMPRODUCT formula returns 226)

What can I do for this situation?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### jasonb75

##### Well-known Member
What can I do for this situation?
Use criteria instead of intersection to identify the correct rows to calculate.

#### jackms

##### New Member
Use criteria instead of intersection to identify the correct rows to calculate.
Thanks. But in my case, there won't be anything in the data that I can use to identify the columns. These columns can only be identified by range name.
(if it makes a difference, certain users will identify the appropriate columns using a macro that will set the "myrange" range name. It will be different columns each time the workbook is used)

#### jasonb75

##### Well-known Member
I did actually say use criteria to identify the rows, not the columns.

In all honesty, sumproduct with non contiguous ranges is never going to work, it will need an alternative but there is not enough information at present to determine what, if anything will work.

Last edited:

#### jackms

##### New Member

I did actually say use criteria to identify the rows, not the columns.

My mistake. But now I don't quite understand your answer. Can you give me an example? Thanks

#### jasonb75

##### Well-known Member
I think you might have missed my edit above, but to clarify. Sumproduct will not work with non contiguous ranges. It may be possible to find an alternative but first we need to know how the ranges are identified.

How did you decide to intersect rows 3 and 6? Why not 5 and 9, or any other combination?

Also, please update your account details (<<link) to show the correct version of excel that you use remember to scroll down and save after choosing one.

#### jackms

##### New Member

Thanks for your help. I will provide a description of what I need to do.
I need a weighted average - average of row 6, weighted by row 3 (those rows are fixed, it will always be row 6 and 3).
If I didn't have to worry about the columns, the complete formula would be =SUMPRODUCT(3:3,6:6)/SUM(3:3)
The trouble is, as I described above, that I need to include in the calculation only certain columns, but those columns will vary each time we retrieve this data. The way we mark those columns is with a range name ("myrange" in my example). In case it's relevant, the columns we need to include in the calculation will be at a certain interval (every 2nd column, or every 3rd column, etc.), but that interval will also vary each time we retrieve the data, and there's no indicator in the data itself what that interval is.

Any ideas how I can accomplish that?

#### jasonb75

##### Well-known Member
Any ideas how I can accomplish that?
I'll try a few things and see if I can find anything that works based on the named range but to be honest, I don't think that it's going to work. It might be necessary to use the code that creates the named range to write the formula in the same syntax as the one you show above.

I assume that the number of columns could vary as well as the interval, which will add to the complication which ever method is used.

#### jackms

##### New Member
I'll try a few things and see if I can find anything that works based on the named range but to be honest, I don't think that it's going to work. It might be necessary to use the code that creates the named range to write the formula in the same syntax as the one you show above.

I assume that the number of columns could vary as well as the interval, which will add to the complication which ever method is used.
You assume correctly. Thank you so much!

#### jasonb75

##### Well-known Member
it will always be row 6 and 3
I missed that bit earlier, that actually makes it a lot easier
Excel Formula:
``=(PRODUCT(myrange 3:3)+PRODUCT(myrange 6:6))/SUM(myrange 3:3)``
If the number of rows was variable then it would not be so simple.

Replies
5
Views
294
Replies
2
Views
323
Replies
7
Views
55
Replies
13
Views
533
Replies
14
Views
271

1,148,423
Messages
5,746,596
Members
424,032
Latest member
pochie2741

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

### Which adblocker are you using?

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

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