Using "If" and "And" in a formula to return a value/number

[edbhome]

I hope this is the correct place to post this and if it isn't I apologize in advance. I have included a small sample of the spreadsheet I'm trying to use in a larger one.

The goal is simple, "If" you select "Yes" to Health insurance, "and" if you select "HMO", I'd like to have the cost for the "HMO" returned in the "Health Cost" cell. The same would hold true for "POS" with the returned cost being the one for "POS." I have started what I think is the right way to do this but so far it's just returning "True" or "false". I didn't go any further with the formula given the simple aspects are not working yet.

Thanks in advance,

Ed

[dhn46]

You use this formulas

Please Login or Register  to view this content.

[kbkumar]

Hi - Try this in F6 and below:

Please Login or Register  to view this content.

Hope this helps...

[edbhome]

You folks are great and I appreciate the prompt relies.

It works if I select HMO but not if I select POS. Additionally, the costs for the HMO and the POS are going to be on a seperate "Variable/Assumtions page (creating a budget with multiple sheets and multiple variable that can be changed) so I would need the caculation to pull the "value" from a specific cell

[Tony Valko]

Try this:

=SUMIFS(Cost,Choice,D6,Health,E6)

[edbhome]

It just returns "#Name?" with no number.

[kbkumar]

....It works if I select HMO but not if I select POS.

Not sure if i follow this. Look at the attached file, seems to work.

[edbhome]

The sheet you sent me works for the first line, but not the second. Maybe I need to restate this ... lets use row "6" as the example, what I'm after is the ability to select if they have health care (Yes/No), then if they do ... you select the type of health care(HMO/POS) and depending on which type you select it pulls the "cost" from the repsective cells ie. if it's HMO then the cost ($400) of HMO (cell K5) gets put into cell F6. If you select POS then the value in cell K6 ($500) gets put into the F6 ... anything else returns either nothing in F6 or $0.00. The same logic goes for row 6 and so on.

Hope this helps to clarify what I'm attempting to do.

As always, I thank everyone for thier assistance. You folks are awsome.

[Tony Valko]

You already have the named ranges Choice and Health so I assumed you would also create the name Cost.

If not just use the range address of the Cost column:

=SUMIFS(K$5:K$6,Choice,D6,Health,E6)

[edbhome]

Thanks to all of you for your assistance. I will mark the thread solved.

[Tony Valko]

You're welcome. We appreciate the feedback!