CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
supercoolville
supercoolville

Reputation: 9086

Sum the value of the second character in a cell?

How can I sum the value of the second character of each cell?

---------------------
| Block A | Block B |
---------------------
| A1221   | R7798   |
| E2154   | E4445   |
| E5442   | G4486   |
| V2211   | C5565   |
| B9984   | E4569   |
---↑---------↑------
   sum       sum

So, for example, I would like to output 19 for "Block A" and 24 for "Block B"

Is this possible?

Upvotes: 0

Views: 511

Answers (2)

oscarius
oscarius

Reputation: 236

To do it in one go without resorting to a help column (and assuming Block A is in column A), you can try this

=SUMPRODUCT(--MID(A1:A5; 2; 1))

Upvotes: 2

Daveo
Daveo

Reputation: 19872

You can use a formula like this For Block A just create a new column C and on the same row as A1221 put this formula

=VALUE(MID(B2,2,1))

That will extract just the first number. Copy and page this formular for all the cells in column c, Then you can so a simple sum of column C

See demo here https://docs.google.com/spreadsheet/ccc?key=0Au2BC1Mvfu_adGpVNkY0WVkweXB5SnpWZHpNc3Uycmc

Upvotes: 1

Related Questions