CODEWITHSUNDEEP
excelexcel-formula
cglf12345
cglf12345

Reputation: 21

How to get a numeric sum for a "character number" within a string in Excel

I have 4 character cells that contain a "number" in positions 1 and 4, and a character in positions 2 and 3. I want to get a numeric sum of the numbers for positions 1 and 4 for the range of cells.

My cells look like this:

cells with numbers in positions 1 and 4, characters in positions 2 and 3

I have tried SUM(MID(VALUE(B4:B21,1,1)) which gives me an invalid type error

Also tried SUM(MID(B4:B21,1,1)*1) which works with a single cell but not for a range.

Upvotes: 1

Views: 454

Answers (2)

VBasic2008
VBasic2008

Reputation: 55073

SUMPRODUCT of LEFT and RIGHT

=SUMPRODUCT(VALUE(LEFT(B4:B21,1)))+SUMPRODUCT(VALUE(RIGHT(B4:B21,1)))

With error handling included, but possibly (Excel 2019) confirming with CTRL+SHIFT+ENTER:

=SUMPRODUCT(IFERROR(VALUE(LEFT(B4:B21,1)),0))+SUMPRODUCT(IFERROR(VALUE(RIGHT(B4:B21,1)),))

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

=SUM(IFERROR(1*MID(B4:B21,{1,4},1),0))

OR 

=SUMPRODUCT(IFERROR(1*MID(B4:B21,{1,4},1),0))

If you have Excel O365 with dynamic arrays, then the first formula will work OK. If you have an earlier version, you may need to either confirm the first formula as an array formula with ctrl+shift+enter, or use the second formula entered normally.

The IFERROR takes care of any values in the range that do not have the pattern of [0-9][A-Z][A-Z][0-9] If you would prefer to detect that by returning an error, just remove the IFERROR part of the formula:

=SUM(1*MID(B4:B21,{1,4},1))

Upvotes: 1

Related Questions