Help with formula's and drop down lists

[Oonar1600]

Hi all, 

I have 4 categories of which each category has a dropdown list with 3 choices. Once all categories have chosen a number from the drop down list, in a different cell I should be able to sum up all the number from each category automatically. The numbers in the drop down are 0.00, 0.25, 0.50. The problem is that I keep getting #value when I add a simple formula to add all the given restults from the drop down list. Not sure what i'm doing wrong here. 

Hope someone can help me! 

Thnx 

[#2]

@Oonar1600 

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

[#3]

I did my best. Here is a link you can use. I hope it works.
https://we.tl/t-Mq5OjZneax

[#4]

Hi @Oonar1600 

Use this formula

=SUM(C4+C8+C12+C16)

[#5]

Hi @Jihad Al-Jarady,

It works and shows as 0 until I start choosing numbers from the drop down menu. As soon as I have chosen those, the formula shoes as #value!

[#6]

Hi @Oonar1600 

It is working, check the file attached.

[#7]

@Jihad Al-Jarady oh that's great! 

I tried again and it's still  not showing up for me. I have attached my screenshot so you can see.  I'm not sure what i'm doing differently than you are. 

[#8]

@Oonar1600 

The problem is caused by different system settings. Apparently, you use comma as decimal separator.

It's best to use a range as source for the data validation drop downs. This avoids the confusion.

See the attached workbook.

[#9]

@Hans Vogelaar 

Okay that is clear. What if I use the decimal instead of comma? will that allow me to not need the source column F shown? I ask because I would prefer not to have the source colom so that it can't accidentally be changed by someone trying to use the sheet. If the answer is yes, how do I go about changing it all so that the system settings are the same and I don't need the extra column. 

[#10]

@Oonar1600 

The advantage of using a range as source for the drop-down lists is that it will keep on working if the end user has a different decimal separator than you - Excel will automatically switch between point and comma as needed.

If you specify the list directly in the Data Validation dialog, it won't work with a different decimal separator.

You can place the source range on a different worksheet, and hide that sheet. That way, the user won't be able to edit the list. See the attached version.

[#11]

@Hans Vogelaar Oh that is a great tip and one I will use! 

Thanks so much for your help and patience in this matter!