SUMPRODUCT with (space) Intersection operator

[jackms]

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?

 

[jasonb75]

What can I do for this situation?

Use criteria instead of intersection to identify the correct rows to calculate.

 

[jackms]

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]

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.

 

[jackms]

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]

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]

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]

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]

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]

it will always be row 6 and 3

I missed that bit earlier, that actually makes it a lot easier

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