Excel Sum Question

[Daniel26]

Hi everyone,

In excel I am curious what steps I need to follow to sum a range of data. For example:

0-2pcs

4pcs

----

Sum (cell1:cell2)

Answer should be: 4-6pcs due to the range. However, using the sum key it returns only 4pcs. How do I enter commands for excel to incorporate the range of the cell? Any help would be greatly appreciated!

[#2]

Let's say, that the cell values are located in column A
If I understood it correctly you're looking for something like this: =SUM(NUMBERVALUE(RIGHT(A:A,1)))

[#3 · Accepted solution]

@Daniel26 

The attached workbook (now a macro-enabled one) contains a custom VBA function for this purpose.

[#4]

The problem is that 0-2 is text not a number (or number range). In fact it appears you had to specifically add the apostrophe before it (force it to be recognized as text).
So I suggest you consider alternative format/configuration for the data. For example you could have 2 columns for min and max then you sum each column and you have min 4 and max 6

[#5]

Hi mtarler,

Thank you for your response. That would be one way of arranging the data for sure.

Best,

Daniel

[#6]

Hi Hans Vogelaar,

This is exactly what I was looking for! Now I just need to teach myself UDF (user defined functions), lol!

Thanks again!

[#7]

Hi Anderszone,

Hope you are well!

I tried entering the script command you recommend and instead of returning the value of 4pcs (as originally the case of my issue), it returned 6pcs. Perhaps it's the way I entered the command. Regardless, thank you for your help!