Combining SUMPRODUCT and INDEX functions

[Ioweyouone]

To all: I'm struggling with this SUMPRODUCT function. Cell B3 needs to add up all the shapes lengths, depending on their quantity. When I remove the blanks and zeros, my formula returns 80, which is incorrect. It should be 61. When finished, this table will have 200 columns and 700 rows of data. Hoping for a simple formula. Thanks.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1
2
3	TOTAL LENGTH OF ALL SHAPES	61
4
5	SHAPES	Total QTY.	Length if 1 Unit	Length if 2 Units	Length if 3 Units	Length if 4 Units
6	Square	1	10	20	30	40
7	Right Triangle	2	10	10	20	20
8	Parallelogram	3	10	18	26	38
9	Triangle	2	10	15	20	25
10	Circle	0	20	30	40	50
11	Line	8
12
Sheet8

Cell Formulas
Range	Formula
B3	B3	=SUM(IFERROR(INDEX(C6:F11,SEQUENCE(6),1/(1/B6:B11)),0))

 

[Ioweyouone]

Eric W,
Thanks for your response. I tried your formula in my sheet, and it seems to work fine (although I don't understand how it works!) Is there a way to change SEQUENCE(6) and make it dynamic to change if I add more rows?

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G
1
2
3	TOTAL LENGTH OF ALL SHAPES	70	70
4
5	SHAPES	Total QTY.	Length if 1 Unit	Length if 2 Units	Length if 3 Units	Length if 4 Units
6	Square	1	10	20	30	40
7	Right Triangle	2	10	10	20	20
8	Parallelogram	3	10	18	26	38
9	Triangle	2	10	15	20	25
10	Circle	0	20	30	40	50
11	Line	8
12	Rhombus	3	1	2	3	4
13	Octagon	2	5	6	7	8
14
Sheet8

Cell Formulas
Range	Formula
B3	B3	=SUM(IFERROR(INDEX(C6:Z1005,SEQUENCE(1000),1/(1/B6:B1005)),0))
C3	C3	=LET(rng_1,A6:A1000,rws,COUNTA(rng_1),qty,1/(1/OFFSET(B6,0,0,rws)),rng_2,OFFSET(C6:F6,0,0,rws),SUM(IFERROR(INDEX(rng_2,SEQUENCE(rws),qty),0)))

The B3 is the same formula, just with the ranges enlarged and the SEQUENCE value increased. The formula is already designed to ignore empty cells, so as long as there's nothing below or to the right, it'll work just fine.

The C3 formula is designed to expand vertically based on the number of values in A6:A1000, so row 1000 is the top. But if you have something in that range not related to your table, that could cause problems. So you might just want to stick with the previous version anyway. It doesn't expand to the right, but that could be done too if you want, but it'll make the formula longer.

Which raises a point, all formulas use the B column to say how far to the right to grab the value, so it will work as long as the values in C5:F5 just increment by 1. If you do something like 1,2,3,4,5,10,20, etc., then we'd need to adapt the formula.

The formula is pretty basic, it uses the SEQUENCE to pick the row, and the B value to pick the column for that row. Then the INDEX looks up the values for each row and SUMs them. The 1/(1/ structure is to turn a 0 into a #DIV/0! error, otherwise INDEX interprets a 0 as a 1.

Hope this helps!

 

[Ioweyouone]

Professor Eric W,
Thank you again for your help. The learning never stops!