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?

 

[Marcelo Branco]

Hi Jason

To work in Excel 2010 I had to make some adjustments to your second formula.
=SUMPRODUCT(INDEX(myrange 3:3,,,N(IF(1,ROW(INDIRECT("1:"&(AREAS(myrange))))))),INDEX(myrange 6:6,,,N(IF(1,ROW(INDIRECT("1:"&(AREAS(myrange))))))))

And confirm it with Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

Adding to my previous post
The formula above doesn't work with the data sample provided by Peter (post #18) because the second part of the range is contiguous (C1:D10)

M.

 

[jasonb75]

Thanks, Marcelo. I was expecting it to be something like that. Functions that were typically array resistant in older versions of excel don't require the same brute force persuasion methods in 365 so it makes accurate testing difficult.

 

[jackms]

=SUMPRODUCT(INDEX(myrange 3:3,,,N(IF(1,ROW(INDIRECT("1:"&(AREAS(myrange))))))),INDEX(myrange 6:6,,,N(IF(1,ROW(INDIRECT("1:"&(AREAS(myrange))))))))

@jasonb75 @Marcelo Branco You guys are the greatest! The formula does exactly what I need!

The formula above doesn't work with the data sample provided by Peter (post #18) because the second part of the range is contiguous (C1:D10)

I was able to tweak the formula for a case where all the columns are contiguous by adding the following to the beginning =IF(AREAS(myrange)=1,SUMPRODUCT(myrange 3:3),
but obviously that wouldn't work for a data sample like Peter's where some columns in the range are contiguous and some are not (but I at least don't have to worry about that because in my application I will never have such a scenario)