SOLVED

Excel Sum Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2782642%22%20slang%3D%22en-US%22%3EExcel%20Sum%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782642%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20excel%20I%20am%20curious%20what%20steps%20I%20need%20to%20follow%20to%20sum%20a%20range%20of%20data.%20For%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E0-2pcs%3C%2FP%3E%3CP%3E4pcs%3C%2FP%3E%3CP%3E----%3C%2FP%3E%3CP%3ESum%20(cell1%3Acell2)%3C%2FP%3E%3CP%3EAnswer%20should%20be%3A%204-6pcs%20due%20to%20the%20range.%20However%2C%20using%20the%20sum%20key%20it%20returns%20only%204pcs.%20How%20do%20I%20enter%20commands%20for%20excel%20to%20incorporate%20the%20range%20of%20the%20cell%3F%20Any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2782642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2782698%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782698%22%20slang%3D%22en-US%22%3ELet's%20say%2C%20that%20the%20cell%20values%20are%20located%20in%20column%20A%3CBR%20%2F%3EIf%20I%20understood%20it%20correctly%20you're%20looking%20for%20something%20like%20this%3A%20%3DSUM(NUMBERVALUE(RIGHT(A%3AA%2C1)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2782746%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165832%22%20target%3D%22_blank%22%3E%40Daniel26%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20attached%20workbook%20(now%20a%20macro-enabled%20one)%20contains%20a%20custom%20VBA%20function%20for%20this%20purpose.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2782752%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sum%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782752%22%20slang%3D%22en-US%22%3EThe%20problem%20is%20that%200-2%20is%20text%20not%20a%20number%20(or%20number%20range).%20In%20fact%20it%20appears%20you%20had%20to%20specifically%20add%20the%20apostrophe%20before%20it%20(force%20it%20to%20be%20recognized%20as%20text).%3CBR%20%2F%3ESo%20I%20suggest%20you%20consider%20alternative%20format%2Fconfiguration%20for%20the%20data.%20For%20example%20you%20could%20have%202%20columns%20for%20min%20and%20max%20then%20you%20sum%20each%20column%20and%20you%20have%20min%204%20and%20max%206%3C%2FLINGO-BODY%3E
New Contributor

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!

 

6 Replies
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)))
best response confirmed by Daniel26 (New Contributor)
Solution

@Daniel26 

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

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
Hi mtarler,

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

Best,

Daniel
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!
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!