4

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.

7

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.

enter image description here

  • 4
    Note that this will return unexpected behavior if you ever have >9 days. It may be safer to instead sum everything to the left of the first space, using something like =SUMPRODUCT(--(LEFT(A1:A4,FIND(" ",A1:A4,1)-1))). Still, +1. – Joe Dec 12 '16 at 20:52
  • 2
    =SUM(VALUE(SUBSTITUTE(A1:A3,"Days","",1))) use this formula as array formula press Ctrl+Shift+Enter and whatever numbers you it will sum it but Days should be the same – yass Dec 12 '16 at 21:28
  • 1
    Nifty, thanks. My example was rubbish as the maximum would be 7 since it's for a week - but something to bear in mind for next time! – NibblyPig Dec 13 '16 at 3:30
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.