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?

 

[jackms]

Thanks. But that's not giving me the correct result.
In my original example: if range myrange consists of A:A,C:C

=(PRODUCT(myrange 3:3)+PRODUCT(myrange 6:6))/SUM(myrange 3:3) = (PRODUCT(3,23)+PRODUCT(6,26))/SUM(3,23) = (69+156)/26 = 8.653846154
but what I want is (PRODUCT(3,6)+PRODUCT(23,26))/SUM(3,23) = (18+598)/26 = 23.69230769

 

[Marcelo Branco]

Maybe it works using the rarely used area_num parameter of the INDEX function

Something like

Pasta1
	A	B	C	D	E	F
1	1	11	21		Areas	Result
2	2	12	22		1	23,69231
3	3	13	23		2
4	4	14	24
5	5	15	25
6	6	16	26
7	7	17	27
8	8	18	28
9	9	19	29
10	10	20	30
Plan1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(INDEX(myrange,3,1,N(IF(1,CHOOSE({1,2},E2,E3)))),INDEX(myrange,6,1,N(IF(1,CHOOSE({1,2},E2,E3)))))/SUM(myrange 3:3)

Syntax
INDEX(reference, row_num, [column_num], [area_num])

M.

​

 

[jasonb75]

If that is not what you want then your question has been wrong from the start, the purpose of sumproduct is to multiply the items in a row, then add the total of the rows together. What you are now asking for is the reverse of that (effectively productsum if such a thing existed).

The result of my formula before division is 225, which is 1 different from what you said it should be in post 1 (which I took to be a simple mental arithmetic error) the figures that you are now saying it should return are nowhere near your original example.

Please check your requirement carefully and clarify exactly what is needed before I waste time looking for a likely impossible solution.

 

[jasonb75]

Maybe it works using the rarely used area_num parameter of the INDEX function

Unlikely Marcelo, it has already been established that the number of columns in myrange (areas for index) can be variable, that is assuming that the information provided was correct, a fact that I am losing faith in.

 

[Marcelo Branco]

Unlikely Marcelo, it has already been established that the number of columns in myrange (areas for index) can be variable

What about this
myrange2 is gray area (A1:A10, C1:C10, F1:F10)

Pasta1
	A	B	C	D	E	F	G	H	I
1	1	11	21	31	41	51		Areas	Result
2	2	12	22	32	42	52		1	45,36709
3	3	13	23	33	43	53		2
4	4	14	24	34	44	54		3
5	5	15	25	35	45	55
6	6	16	26	36	46	56
7	7	17	27	37	47	57
8	8	18	28	38	48	58
9	9	19	29	39	49	59
10	10	20	30	40	50	60
Plan2

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(INDEX(myrange2,3,1,N(IF(1,CHOOSE({1,2,3},H2,H3,H4)))),INDEX(myrange2,6,1,N(IF(1,CHOOSE({1,2,3},H2,H3,H4)))))/SUM(myrange2 3:3)

M.

 

[jackms]

If that is not what you want then your question has been wrong from the start, the purpose of sumproduct is to multiply the items in a row, then add the total of the rows together. What you are now asking for is the reverse of that (effectively productsum if such a thing existed).

I sincerely apologize if I wasn't clear. In the following

the formula =SUMPRODUCT(A3:C3,A6:C6) = (PRODUCT(3,6)+PRODUCT(23,26)) = 616.
My issue is that in my spreadsheet, column B also has data, so I need to skip column B when doing SUMPRODUCT.

(When I said "if myrange would consist of A:B, then the above SUMPRODUCT formula returns 226", then it would be the equivalent of =SUMPRODUCT(A3:B3,A6:B6) which in the spreadsheet in my original message =226)

 

[jackms]

What about this
myrange2 is gray area (A1:A10, C1:C10, F1:F10)

Pasta1
	A	B	C	D	E	F	G	H	I
1	1	11	21	31	41	51		Areas	Result
2	2	12	22	32	42	52		1	45,36709
3	3	13	23	33	43	53		2
4	4	14	24	34	44	54		3
5	5	15	25	35	45	55
6	6	16	26	36	46	56
7	7	17	27	37	47	57
8	8	18	28	38	48	58
9	9	19	29	39	49	59
10	10	20	30	40	50	60
Plan2

Cell Formulas
Range	Formula
I2	I2	=SUMPRODUCT(INDEX(myrange2,3,1,N(IF(1,CHOOSE({1,2,3},H2,H3,H4)))),INDEX(myrange2,6,1,N(IF(1,CHOOSE({1,2,3},H2,H3,H4)))))/SUM(myrange2 3:3)

M.

Ingenious! I never knew about using N in that way. But I don't think this will solve my problem. I don't know in advance how many areas there will be. So if myrange2 would only have 2 areas, then the formula returns #VALUE.

 

[Peter_SSs]

What about a user-defined function?

Function WA(rng As Range, Optional FirstRow As Long = 3, Optional LastRow As Long = 6) As Double
  Dim rA As Range, col As Range
  Dim prod As Double, sm As Double
 
  For Each rA In rng.Areas
    For Each col In rA.Columns
      prod = prod + col.Cells(FirstRow).Value * col.Cells(LastRow).Value
      sm = sm + col.Cells(FirstRow).Value
    Next col
  Next rA
  WA = prod / sm
End Function

myRange is columns A and C:D. I have assumed myRange consists of entire columns.
Green cells were just for my benefit while checking.

jackms.xlsm
	A	B	C	D	E	F	G
1	1	11	21	31	41		30.57627119
2	2	12	22	32	51
3	3	13	23	33	61
4	4	14	24	34	71
5	5	15	25	35	81
6	6	16	26	36	91
7	7	17	27	37	101
8	8	18	28	38	111
9	9	19	29	39	121
10	10	20	30	40	131
Sheet1

Cell Formulas
Range	Formula
G1	G1	=WA(myRange)

 

[jackms]

What about a user-defined function?

Function WA(rng As Range, Optional FirstRow As Long = 3, Optional LastRow As Long = 6) As Double
  Dim rA As Range, col As Range
  Dim prod As Double, sm As Double
 
  For Each rA In rng.Areas
    For Each col In rA.Columns
      prod = prod + col.Cells(FirstRow).Value * col.Cells(LastRow).Value
      sm = sm + col.Cells(FirstRow).Value
    Next col
  Next rA
  WA = prod / sm
End Function

myRange is columns A and C:D. I have assumed myRange consists of entire columns.
Green cells were just for my benefit while checking.

jackms.xlsm
	A	B	C	D	E	F	G
1	1	11	21	31	41		30.57627119
2	2	12	22	32	51
3	3	13	23	33	61
4	4	14	24	34	71
5	5	15	25	35	81
6	6	16	26	36	91
7	7	17	27	37	101
8	8	18	28	38	111
9	9	19	29	39	121
10	10	20	30	40	131
Sheet1

Cell Formulas
Range	Formula
G1	G1	=WA(myRange)

Thank you! Great idea! But for my purpose, the workbook can't use VBA. It has to be a xlsx workbook.

 

[jasonb75]

Playing around with @Marcelo Branco's earlier suggestions, I've managed to get 2 working formulas. The first will only work with office 365 / excel 2021. The second should work back as far as 2007 if the information on microsoft support is correct, however I have not been able to test it in anything other than 365. It is often the case with formulas like this that some additional persuasion in the form of N(IF({1} or similar is needed with older versions.

=LET(s,SEQUENCE(AREAS(myrange)),SUMPRODUCT(INDEX(myrange 3:3,,,s),INDEX(myrange 6:6,,,s)))

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

Both formulas return a result of 616 using the clarified test criteria.