How do I create a formula for a checklist that if checked picks values from dispersed cells?

[edude]

Hi, I'm trying to do this elegantly

=SUM(IF(E7:U7="x";value1 for E7, value2 for F7, value3 for G7...;0)
The cell in the right should be a a formula that sums all the elements checklisted with an x. Each of those cells it's a checklist of elements with a cost assigned in dispersed cells that are added up in the cell in the right.

Right now the formula looks like this and works but i'm guessing there is a better way to do it

 

[Georgiboy]

Below is a version where you would create a lookup table for the dispersed cells, the lookup table would hold the column numbers of the headers in the main table. I then looks at the column where it finds an "x" and looks it up from the table and then sums those values.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Bread	Jam	Cheese	Onions	Peas	Crisps	Juice	Total			Lookup table					dispersed cells
2	Food Delvery 1	x	x		x	x		x	£8.00			Column	Food	Price			Bread	£1.00
3	Food Delvery 2			x			x		£4.00			2	Bread	£1.00
4												3	Jam	£1.00			Cheese	£1.00
5												4	Cheese	£1.00
6												5	Onions	£2.00
7												6	Peas	£1.00			Jam	£1.00
8												7	Crisps	£3.00
9												8	Juice	£3.00				Onions	£2.00
10
11
12																	Peas	£1.00
13
14																			Crisps	£3.00
15
16
17																	Juice	£1.00
18																	Juice	£1.00
19																	Juice	£1.00
Sheet1

Cell Formulas
Range	Formula
I2:I3	I2	=SUM(XLOOKUP(IF(B2:H2="x",COLUMN(B2:H2),""),$L$3:$L$9,$N$3:$N$9,0))
N3,N5	N3	=R2
N4	N4	=R7
N6	N6	=S9
N7	N7	=R12
N8	N8	=T14
N9	N9	=SUM(R17:R19)

 

[Peter_SSs]

This is reasonably similar to the previous suggestion but doesn't require a lookup as such. It does, however, rely on things being put in the correct order at set-up.

I can't quite follow your formula since you mentioned cells E7:U7 but what we can see of the formula it appears to skip cells F7, G7 & H7.

Anyway, see if this is some use. It is a smaller example but hopefully shows the idea.
Set up a range of cells in 'Elementos' (though it could be anywhere really) like I have in AA1:AD1. The formula in each of those cells should point to the relevant cell in the relevant order. That is the first cell (AA7) should point to the relevant cell for if E7 on the formula sheet has an "x", the second cell (AB7) should point to the relevant cell for if F7 on the formula sheet has an "x" etc
This row or these columns could then be hidden if you want.

edude.xlsm
	J																Z	AA	AB	AC	AD
1																		5	3	66	9
2
5
6	5
7
14
15	66
16
19
20	9
21
28
29	3
Elementos

Cell Formulas
Range	Formula
AA1	AA1	=J6
AB1	AB1	=J29
AC1	AC1	=J15
AD1	AD1	=J20

Then you can use a formula like this to get the sum you want.

edude.xlsm
	E	F	G	H														V	W
7	x		x	x															80
8		x																	3
9																			0
10	x	x	x	x															83
11	x			x															14
Sheet1

Cell Formulas
Range	Formula
W7:W11	W7	=SUM(FILTER(Elementos!AA$1:AD$1,E7:H7="x",0))

 

[edude]

Thanks guys you are great. In the end, inspired with your recommendation i just organized all the dispersed values in a table and did it with a regular sumif.

 

[Peter_SSs]

Cheers. Glad you have it sorted. Thanks for letting us know.