Excel SUMIF with INDEX and MATCH

S

Sontu

New Member
Joined
Mar 1, 2018
Messages
2
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.

[TABLE="width: 1042"]
<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>[TR]
[TD] [/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Alpha[/TD]
[TD]Neutron[/TD]
[TD]Gamma [/TD]
[/TR]
[TR]
[TD]Spec A[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]28[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Neutron[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]Gamma [/TD]
[TD]Alpha[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Beta[/TD]
[TD]Gamma[/TD]
[TD]X-Ray[/TD]
[TD]Beta[/TD]
[TD]Alpha[/TD]
[TD]Neutron[/TD]
[/TR]
[TR]
[TD]Spec B[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]27[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Neutron[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]Gamma [/TD]
[TD]Alpha[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Neutron[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Alpha[/TD]
[TD]Beta[/TD]
[TD]X-Ray [/TD]
[TD]Gamma [/TD]
[TD]Beta[/TD]
[TD]Neutron[/TD]
[TD]X-Ray[/TD]
[TD]Alpha[/TD]
[TD]Gamma[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD]Spec C[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Based on your data above try:
The formula in B3 can just be copied across and then copied to the other rows.
Excel Workbook
ABCDEFGHIJKLMN
1AlphaBetaX-RayGammaNeutron`
2AlphaBetaGammaNeutronAlphaBetaX-Ray
3Spec A1744283828124361210
4NeutronX-RayGammaNeutronAlphaBetaGamma
5Spec B2641353927891271046
6NeutronX-RayGammaNeutronAlphaBetaGamma
7Spec C333638382344878109
Sheet
 
Upvote 0
AhoyNC said:
Based on your data above try:
The formula in B3 can just be copied across and then copied to the other rows.

ABCDEFGHIJKLMN
`
AlphaBetaGammaNeutronAlphaBetaX-Ray
Spec A
NeutronX-RayGammaNeutronAlphaBetaGamma
Spec B
NeutronX-RayGammaNeutronAlphaBetaGamma
Spec C

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Alpha[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Beta[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]X-Ray[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Gamma[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Neutron[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]28[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]41[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]35[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]39[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]27[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]33[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]36[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]23[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...

Thanks mate. It works. I realised i was complicating things for no reason.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
E
  • Solved
in a tangle with.... multiple-conditions-&-possibilities-for-multiple-ifs
Replies
5
Views
255
BigBeachBananas
B
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
279
myall_blues
myall_blues
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
258
myall_blues
myall_blues
E
  • Solved
Find cells which start (not contain) search strings
Replies
5
Views
343
Alex Blakenburg
A

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top