Sumproduct workaround

[erniepoe]

Hi All,

Wondering if there is a workaround for a sumproduct formula where I wouldnt have an equal array size. In the below, obviously it is erroring out due to the 5. Unfortunately, putting 5 in C32:C35 isn't an option, but I'd still like to calculate the sumproduct of A*B*5.

Is there a way to get around this issue? Thank you so much for any help.

=SUMPRODUCT(A32:A35,B32:B35,5/SUM(A32:A35))

 

[lrobbo314]

Is this what you're looking for?

erniepoe
	A	B	C	D
32	5	2		30
33	10	4
34	15	6
35	20	8
Sheet7

Cell Formulas
Range	Formula
D32	D32	=SUMPRODUCT(A32:A35,B32:B35,INDEX(5/SUM(A32:A35),SEQUENCE(ROWS(A32:A35),,,0)))

 

[Dave Patton]

SumProduct2022a.xlsm
	A	B	C	D
32	1	10		22
33	2	5		22
34	3	4
35	4	3
7c

Cell Formulas
Range	Formula
D32	D32	=SUMPRODUCT(A32:A35,B32:B35)*5/SUM(A32:A35)
D33	D33	=SUMPRODUCT(A32:A35*B32:B35*5)/SUM(A32:A35)

It would be helpful if you posted the information with the forum's tool named XL2BB.

What is the expected result?

 

[Dave Patton]

SUM(A32:A35*B32:B35*5)/SUM(A32:A35)