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?
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?