How to sum one cell based on multiple cells

[Emma Eve]

I apologize for uploading just an image. I tried to install the xl2BB and could not get it to work despite following each links instructions.

Using the image to describe the problem, I am having difficulty summing the number of inputs based on how many times a component appears.

For example, the yellow cell needs to return the number of Input Alpha based on the Components in that row. In this case, the result of the formula should be 10 because Component A appears twice with one input for each alpha, Component B appears once with two inputs for each alpha and Component C appears twice with three inputs for each alpha. Following the same logic, the results for input Charlie in the orange cell should be 40. I have tried SUMIFS, INDEX/MATCH, VLOOKUPS, COUNTIFS.

 

[steve the fish]

We dont have any cell refs but something like this can be adapted:

=SUMPRODUCT(SUMIFS(I2:I4,H2:H4,A8:E8))

 

[lrobbo314]

How about this? You may need to do Ctrl+Shift+Enter depending on your version of Excel.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4	1	2	3	4	5			Alpha	Bravo	Charlie	Delta			Component	Alpha	Bravo	Charlie	Delta
5	A	A	B	C	C			10	20	40	50			A	1	2	7	8
6	A	A	B	B	B			8	16	38	46			B	2	4	8	10
7														C	3	6	9	12
Sheet3

Cell Formulas
Range	Formula
H5:K6	H5	=SUM(INDEX(O$5:O$7,MATCH($A5:$E5,$N$5:$N$7,0)))

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
16	Components Table													Inputs
17	1	2	3	4	5	Grouped	Day	Alpha	Bravo	Charlie	Delta			Component	Alpha	Bravo	Charlie	Delta
18	A	A	B	C	C	A,A,B,C,C	First Set - Part 1	10	20	40	50			A	1	2	7	8
19	A	A	B	B	B	A,A,B,B,B	Second Set - Part 2	8	16	38	46			B	2	4	8	10
20														C	3	6	9	12
Sheet4

Cell Formulas
Range	Formula
H18:K19	H18	=SUMPRODUCT(SUMIF($N$18:$N$20,$A18:$E18,O$18:O$20))
F18:F19	F18	=TEXTJOIN(",",1,A18:E18)

 

[Emma Eve]

Thank you all for your responses! I plugged the formulas into the spreadsheet and it works perfectly!

 

[Eric W]

Glad we could help! Thanks for the update!