Sum question

[GAJITCS]

Please excuse what will be a question with the most simple answer, but I'm suffering with Brain Fog today.

From the following collection of cells:

	A	B	C	D	E	F	G
1	0.75
2		0.25	0.50	0.75	1.00	2.00	3.00
3		1.44	1.46	1.53	1.68	2.32	3.01
4	50	1.78	1.80	1.88	1.92	2.40	3.22
5		0.48	0.60	0.70	0.80	0.90	1.00
6	25	1.23	1.26	1.35	1.40	1.88	2.52
7		1.60	1.70	1.80	1.90	2.22	3.33
8	10	0.97	1.02	1.30	1.55	1.75	1.99
9		1.41	1.43	1.66	1.84	1.98	2.04
10	50	1.22	1.25	1.63	1.80	2.21	2.84
11

<tbody>
</tbody>

What I need to achieve in cell A11, is to multiply the values in range A2:A10 by the values in another column where the cell value in A1 is a match to a value in range B2:G2

I'm sure I've done this before and that it's not difficult, I'm just having a problem today getting my head around the logic.

The answer in this case should be the equivalent of adding the sums of (A4*D4), (A6*D6), (A8*D8) and (A10*D10) = 222.25

Many thanks. Hoping the fog will clear by morning.

 

[Marcelo Branco]

Re: Sum question - Brain Fog

Try

=SUMPRODUCT(A2:A10,INDEX(B2:G10,0,MATCH(A1,B2:G2,0)))

M.

 

[Eric W]

Re: Sum question - Brain Fog

Or:

=SUMPRODUCT(A2:A10*B2:G10*(B2:G2=A1))

 

[shg]

Re: Sum question - Brain Fog

It doesn't matter because of the small size of the data, but that's way slower than Marcelo's formula, Eric:

	M​	N​	O​	P​	Q​	R​	S​	T​
3​	Range​	Formula​	# Cells​	Iterations​	Total Calcs​	Time​

[td="bgcolor:#F3F3F3"]

Time/Calc​

[/td][td="bgcolor:#F3F3F3"]

Rel Speed​

[/td]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]I2:I101[/td][td] =SUMPRODUCT(A2:A10,INDEX(B2:G10,0,MATCH(A1,B2:G2,0)))[/td][td]

100​

[/td][td]

16,384​

[/td][td]

1,638,400​

[/td][td]

2.188​

[/td][td="bgcolor:#E5E5E5"]

0.000 001 335​

[/td][td="bgcolor:#E5E5E5"]

4.3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]K2:K101[/td][td] =SUMPRODUCT(A2:A10*B2:G10*(B2:G2=A1))[/td][td]

100​

[/td][td]

4,096​

[/td][td]

409,600​

[/td][td]

2.359​

[/td][td="bgcolor:#E5E5E5"]

0.000 005 760​

[/td][td="bgcolor:#E5E5E5"]

1.0​

[/td][/tr]

 

[Eric W]

Re: Sum question - Brain Fog

I suspected it would be. Marcelo's formula essentially performs a sequential search on a 1X6 array, then performs 9 multiplications and a sum. Mine performs 6*9*6=324 multiplications and a sum. I'm a bit surprised that the difference is only a factor of 4.3. But as you said, on a small range the actual time difference is negligible, and perhaps the shorter formula might be preferable for clarity.

In any case, thanks for calculating the timing. This gives the OP more information with which to choose a formula that best suits him/her.

 

[Marcelo Branco]

Re: Sum question - Brain Fog

In addition to the performance I think it's important that people learn to use the INDEX function in all its functionality. It is fast and flexible and few are aware of the ability to return an array of values using row_num = 0 to return all rows or col_num = 0 to return all columns.

M.

 

[Marcelo Branco]

Re: Sum question - Brain Fog

In addition to the performance I think it's important that people learn to use the INDEX function in all its functionality. It is fast and flexible and few are aware of the ability to return an array of values using row_num = 0 to return all rows or col_num = 0 to return all columns.

M.

Eric, of course this comment is not addressed to you

M.

 

[Eric W]

Re: Sum question - Brain Fog

Eric, of course this comment is not addressed to you

M.

No worries! I agree, the ability of INDEX to return an array is one of its coolest, most valuable, and unknown properties. I was working on a very similar formula to yours, but you beat me to it, so I decided to highlight a different way to use SUMPRODUCT. But as you and shg both pointed out, the choice of which formula is "best" depends on a lot of factors, some of which are not very obvious.

 

[shg]

Re: Sum question - Brain Fog

The formula timer workbook is available at https://app.box.com/s/b5ykwwa9x6xs8d7v9vhv

 

[GAJITCS]

Re: Sum question - Brain Fog

I thought I had replied to this yesterday, but can't see my post now.

Thank you everyone for your help. Both solutions worked.

I did ask about the formula timer, so thank you also Shg for posting the link.