My column has data such as:
3 DAYS
5 DAYS
8 DAYS
I'd like to sum the number of days.
I can do LEFT(B2, 1)
for example to get the number, however I am not sure how to combine that with SUM
to create a total.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityMy column has data such as:
3 DAYS
5 DAYS
8 DAYS
I'd like to sum the number of days.
I can do LEFT(B2, 1)
for example to get the number, however I am not sure how to combine that with SUM
to create a total.
Use SUMPRODUCT to wrap your LEFT formula:
=SUMPRODUCT(--(LEFT(A1:A3,1)))
The --
does the same as VALUE()
. It turns the text value that is returned by the LEFT() function into a number.
Click on data option in toolbar and select text to columns
Then delimit the columns by space ,then the string is separated from numerical
Now you can sum the column