CODEWITHSUNDEEP
excelexcel-formulasum
joon25
joon25

Reputation: 41

Sum alphanumeric cells in a column Excel

I have a column that I would like to sum all numbers within. Each cell within the column starts with a string in the beginning:

BL1.00
HB1.00
HP0.50
HF0.25
LP1.00

=3.75

Some cells have 2 decimal points, how do I sum this column all the way to the 2 decimals?

Upvotes: 1

Views: 908

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Assuming that all of the cells in question begin with two characters which are to be ignored, you could use an Array Formula.

If your data (from your example) is in cells A1:A5, then you could use Array Formula:

=SUM(VALUE(MID(A1:A5,3,99)))     → → →     [CTRL]+[SHIFT]+[ENTER]

Copy the formula above and paste it into the formula bar.

Since it's an array formula, instead of hitting Enter, you need to hold down CTRL and SHIFT and then press ENTER.

img

More Information:

Upvotes: 2

Related Questions