CODEWITHSUNDEEP
excel-formula
Charles
Charles

Reputation: 23

excel-formula sum over 1st character within a range in which 1st character is always an integer

I have a column of survey responses to a question in which the data is an integer 1 through 5 inclusive followed by text (e.g., "5 stars - I love it"). I want to sum the integers in the 1st characters. The dirty way would be to create a new column in which all but the first character is stripped, but I thought I'd be slick and avoid this.

However, my CSE array formula seems to be summing COUNTA(...) instead of summing over the LEFT(...,1) in the cells

{=SUM(NUMBERVALUE(LEFT(AC$62:AC$9999,1)))}

I'm wondering why there's an implicit COUNTA included here and whether CSE formula is not a good way to approach this.

Upvotes: 2

Views: 244

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

with data in A1 through A5, use:

=SUMPRODUCT(--LEFT(A1:A5,1))

enter image description here

As you see, the formula discards everything after the leading digit.

NOTE:

If you replace blanks with zeros, the formula will work.

EDIT#2:

You can avoid the helper column if you use the array formula:

=SUMPRODUCT(--(LEFT(IF(A1:A6="",0,A1:A6),1)))

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

enter image description here

Upvotes: 1

Related Questions