Excel SUMIF with INDEX and MATCH

[Sontu]

I have a set of data that looks like the attached table. I want to be able to sum all the values that fall under the Alphas, Betas, X-Rays etc. for a particular specimen. I suppose the SUMIF function with INDEX and MATCH should work, however i am unable to put the formula together. Any help would be much appreciated.

	Alpha	Beta	X-Ray	Gamma	Neutron
							Alpha	Beta	Gamma	Neutron	Alpha	Beta	X-Ray	Beta	X-Ray	Gamma	Neutron	Beta	X-Ray	Alpha	Beta	X-Ray	Gamma	Neutron	Beta	X-Ray	Alpha	Neutron	Gamma
Spec A	17	44	28	38	28		1	2	4	3	6	12	10	8	3	12	8	9	2	6	8	5	10	7	5	8	4	10	12
							Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma	Alpha	X-Ray	Gamma	Neutron	Beta	X-Ray	Alpha	Beta	X-Ray	Gamma	Beta	Gamma	X-Ray	Beta	Alpha	Neutron
Spec B	26	41	32	29	27		8	9	12	7	10	4	6	7	10	7	6	8	8	1	12	5	4	8	10	3	9	8	6
							Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma	Alpha	X-Ray	Gamma	Neutron	Beta	X-Ray	Alpha	Beta	X-Ray	Gamma	Beta	Neutron	X-Ray	Alpha	Gamma	Beta
Spec C	33	36	34	26	23		4	4	8	7	8	10	9	12	6	1	5	2	12	8	6	12	8	10	7	4	5	12	8

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[AhoyNC]

Based on your data above try:
The formula in B3 can just be copied across and then copied to the other rows.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Alpha	Beta	X-Ray	Gamma	Neutron					`
2								Alpha	Beta	Gamma	Neutron	Alpha	Beta	X-Ray
3	Spec A	17	44	28	38	28		1	2	4	3	6	12	10
4								Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma
5	Spec B	26	41	35	39	27		8	9	12	7	10	4	6
6								Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma
7	Spec C	33	36	38	38	23		4	4	8	7	8	10	9
Sheet

 

[Sontu]

Based on your data above try:
The formula in B3 can just be copied across and then copied to the other rows.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Alpha	Beta	X-Ray	Gamma	Neutron					`
2								Alpha	Beta	Gamma	Neutron	Alpha	Beta	X-Ray
3	Spec A	17	44	28	38	28		1	2	4	3	6	12	10
4								Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma
5	Spec B	26	41	35	39	27		8	9	12	7	10	4	6
6								Neutron	X-Ray	Gamma	Neutron	Alpha	Beta	Gamma
7	Spec C	33	36	38	38	23		4	4	8	7	8	10	9

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:31px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B3 =SUMIF($H2:$AD2,B$1,$H3:$AD3) B5 =SUMIF($H4:$AD4,B$1,$H5:$AD5) B7 =SUMIF($H6:$AD6,B$1,$H7:$AD7) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks mate. It works. I realised i was complicating things for no reason.