Sum Formula for Multiple criteria lookup

[kjonesie07]

Hello,

I need a formula to sum the totals of the States. Novice user, apologies.

 

[PeteWright]

Is it really this simple or have I missed something?

MrExcel_202409.xlsm
	A	B	C	D	E
1	State	Number
2	AK	303		642	= AK + DC + AR + AZ + CT
3	AL	106		1623	= AL + CA + CT + DC
4	AR	71
5	AZ	211
6	CA	1460
7	CO	441
8	CT	49
9	DC	8
Sheet3

Cell Formulas
Range	Formula
D2	D2	= B2 + B9 + B4 + B5 + B8
D3	D3	= B3 + B6 + B8 + B9

If you like, you can use "Names" for the cells, like _AL, .AL or 'AL

If you need to set up a Name (like a variable) just enter it into the Name Box

After you have given Names to your cells, they can be distinguished much easier.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=SUM(SUMIFS(B:B,A:A,E2:I2))

 

[kjonesie07]

Is it really this simple or have I missed something?

MrExcel_202409.xlsm
	A	B	C	D	E
1	State	Number
2	AK	303		642	= AK + DC + AR + AZ + CT
3	AL	106		1623	= AL + CA + CT + DC
4	AR	71
5	AZ	211
6	CA	1460
7	CO	441
8	CT	49
9	DC	8
Sheet3

Cell Formulas
Range	Formula
D2	D2	= B2 + B9 + B4 + B5 + B8
D3	D3	= B3 + B6 + B8 + B9

If you like, you can use "Names" for the cells, like _AL, .AL or 'AL

If you need to set up a Name (like a variable) just enter it into the Name Box

View attachment 116565

After you have given Names to your cells, they can be distinguished much easier.

I want it to lookup automatically, there's lots of data, with all states, and the states to sum change out. Apologies for not saying that.

 

[kweaver]

Maybe this?

Book2
	A	B	C	D	E	F	G	H	I
1	State	Number		Sum
2	AK	303		721	AK	DC	AR	AZ	CT
3	AL	106		1702	AL	CA	CT	DC
4	AR	71
5	AZ	211
6	CA	1460
7	CO	441
8	CT	49
9	DC	87
Sheet2

Cell Formulas
Range	Formula
D2:D3	D2	=SUM(INDEX($B$2:$B$100,XMATCH(E2:Z2,$A$2:$A$100)))