SUMPRODUCT with (space) Intersection operator

jackms

New Member
Joined
Dec 28, 2005
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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
1635177423212.png


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

jackms

New Member
Joined
Dec 28, 2005
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 28, 2005
Messages
35
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 28, 2005
Messages
35
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 28, 2005
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,572
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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
Top