Sum values based on their 'code'

[SanctiMortem]

Hi all,

I'm trying to find a way of adding a total value based on the selection of two different cells. These two cells have a drop down list, each of the options on that drop down list has a value:



The first image shows the drop list on the cells, and the second is a different sheet with the values.

Once you select your option from that drop down list, I need another cell to SUM the values corresponding to the selection.

For example, if I select Annual on the Insurance and Yes on the Rain Insurance, then I need the next cell to Add 130 plus 10 and give me the total of 140.
If the next row has Daily and Yes on the Rain Insurance then I need the next cell to add 25 plus 10 and give me 35.

How can I do this, I know its possible, but I'm trying not ti have a crazy formula of sumif and if or else and such if possible, I feel like those formulas on the larger scale can affect the performance of the file.

But then again, any solutions are welcome!
Thank you in advance.

Best Regards!

 

[Aladin Akyurek]

Let B2 house a Rain insurance selection ( B1 houses "Rain Insurance") ;

Name the area housing the two rows with Annual and FF ValuesTable ;

Name the area housing the two rows with Yes and No YesNoTable ;

In B3 enter either Yes or No ;

In B4 enter ;

=IF(B2="",0,VLOOKUP(B2,ValuesTable,2,0)+VLOOKUP(B3,YesNoTable,2,0)

 

[gravanoc]

It sounds like VLOOKUP would be good here, wrapped in SUM. So in your sum cell: =SUM(VLOOKUP(Drop-down Cell, Range of possible options & their values, 2, FALSE), VLOOKUP(Rain Drop-down Cell, Range of possible options & their values, 2, FALSE))

 

[jtakw]

Hi,

Would this work for you also:

Book3.xlsx
	A	B	C	D	E	F	G	H
1					Annual	130	Yes	10
2	Daily	Yes	35		Daily	25	No	0
3					LCN	0
4					FF	0
Sheet1046

Cell Formulas
Range	Formula
C2	C2	=SUMIF(E$1:E$4,A2,F$1:F$4)+SUMIF(G$1:G$2,B2,H$1:H$2)

 

[SanctiMortem]

Hi all!!

Thank you for the quick responses!! I’ll give it a shot and see which one works best!! Thank you!