Need cell to be a number when a word is chosen from a dropdown and then added to other cel

**Compliance Queen** · 01-02-2018, 01:30 PM

I'm going to try and explain this as best as I can.

I have a drop down in X9 with 3 different text options, each one equals a number in cell AB9. The formula in AB9 needs to add the amount chosen in XP and add the amounts in T9, U9, V9, and W9.

Here are my attempts to make this happen and none of them work.

=SUM(COUNTIF(X9"=No Transfer",30),COUNTIF(X9"=Transfer",15),COUNTIF(X9"=eHealth",15),T9:W9)

=IFERROR(IF(G16<100%,"FAIL",SUM(COUNTIF(F17:F18,"=No"),COUNTIF(F17:F18,"=Yes"))),0)

=SUM(COUNTIF(X9="No Transfer",30),COUNTIF(X9"=Transfer",15),COUNTIF(X9"=eHealth",15),T9:W9)

This is what it looks like. I am trying add the prices for stand alone health products in T9-W9 with either $30 for "No Transfer" or $15 for "Transfer in house" or "Transfer eHealth" to equal the correct number in AB9.
Notice Rows 10 and 11 don't add the $15

Capture.PNG

**AliGW** · 01-02-2018, 01:34 PM

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**davesexcel** · 01-02-2018, 01:35 PM

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

To add a file to a post

**Compliance Queen** · 01-02-2018, 02:32 PM

The numbers can be anything in T through W.
No Transfer= 30
Trans Inhouse= 15
Trans eHealth=15

I put in the notes what each one is supposed to equal. If you notice only No Transfer works correctly.

**Compliance Queen** · 01-02-2018, 02:33 PM

Thank you!

**johnmpl** · 01-02-2018, 02:42 PM

Hi, to all!

For your particular examples, you can use:
[AB8] : =SUM(T8:W8,15+15*(X8="No Transfer"))

Blessings!

**JohnTopley** · 01-02-2018, 02:46 PM

In AB8

=IFERROR(SUM(T8:W8)+VLOOKUP(X8,$AL$8:$AN$10,3,0),0)

Copy down

**samba_ravi** · 01-02-2018, 02:54 PM

ab8=SUM(T8:W8,IFERROR(INDEX({30,15,15},MATCH(X8,{"No Transfer","Trans Inhouse","Trans eHealth"},0)),0))
try this and copy towards down
Note: in your dropdown list (column x), change Trans eHeatlh as Trans eHealth

**Compliance Queen** · 01-02-2018, 02:57 PM

X is a drop down menu with three options.
No Transfer
Trans Inhouse
Trans eHealth

When selected it is supposed to add 30 for "No Transfer" or 15 for "Trans Inhouse", or 15 for "Trans eHealth" in AB.

All of that is to be added to T, U, V, W to give the final answer in AB.

Example:
T8 = $10, U8 = $5, V8= $0 and W8 = $0. All these need to be added together. Then X8 "No Transfer" was chosen so that is $30.
all this together would be $45 in AB8
Another Example:
T8 = $21, U8 =15, V8 = $2, W8 = $7, X8 "Trans Inhouse" = $15: $60 in AB8

**Compliance Queen** · 01-02-2018, 03:01 PM

Everything works but eHealth. When I test it the eHealth option doesn't add $15 to AB

**samba_ravi** · 01-02-2018, 03:10 PM

Originally Posted by Compliance Queen

X is a drop down menu with three options.
No Transfer
Trans Inhouse
Trans eHealth

When selected it is supposed to add 30 for "No Transfer" or 15 for "Trans Inhouse", or 15 for "Trans eHealth" in AB.

All of that is to be added to T, U, V, W to give the final answer in AB.

Example:
T8 = $10, U8 = $5, V8= $0 and W8 = $0. All these need to be added together. Then X8 "No Transfer" was chosen so that is $30.
all this together would be $45 in AB8
Another Example:
T8 = $21, U8 =15, V8 = $2, W8 = $7, X8 "Trans Inhouse" = $15: $60 in AB8

Please look at your dropdown there is a spelling mistake,
it is not "Trans eHealth" in your dropdown change the dropdown list

**JohnTopley** · 01-02-2018, 03:11 PM

Did you try solution in #7?

**Compliance Queen** · 01-02-2018, 03:28 PM

Ha! I am dyslexic. If you wouldn't have pointed that out I wouldn't have seen it. I actually checked for misspellings and didn't see it.

Thank you so much!!!!

Need cell to be a number when a word is chosen from a dropdown and then added to other cel

LinkBack

Thread Tools

Rate This Thread

Display

Need cell to be a number when a word is chosen from a dropdown and then added to other cel

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Re: Need cell to be a number when a word is chosen from a dropdown and then added to other

Thread Information

Users Browsing this Thread

Similar Threads

Adding a value to a cell based on text chosen from a dropdown?

Set value of cell if text word is chosen

User Iput via Dropdown Box and then Separate Cell Calculates based on Options Chosen

Changing number of days in a month based on month chosen from a dropdown list.

Identify who has been chosen from a dropdown, and add information onto a different sheet.

Output value based on what is chosen in dropdown list

Increasing Values by % chosen from a dropdown list/box.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions