Sumif and Left/Mid formula

[John Michl]

I need to sum a column of numbers that meet a criteria that is based on one
character within a string. The string is a seven character string that has
about 125 possible combinations. A simplified version is below. What
formula would I used to sum all records that have "A" in the first position.
The answer should be 6.

AB 2
Ax 4
xB 5



Thanks. - John

 

[Ken Wright]

If all the data is as per your example, ie text with one space then followed by the number you
wish to sum, you could use the following:-

=SUM(IF(LEFT(A1:A7)<>"A",0,--MID(A1:A7,FIND(" ",A1:A7)+1,LEN(A1:A7))))

array entered CTRL+SHIFT+ENTER

 

[Ken Wright]

Without needing an array:-

=SUMPRODUCT((LEFT(A1:A7)="A")*MID(A1:A7,FIND(" ",A1:A7)+1,LEN(A1:A7)))

 

[John Michl]

Thanks, Ken. My example wasn't clear enough regarding the rest of the data
set but you provided enough of an example for me to get it to work. I had
already tried using Sumproduct but it didn't work for me. I was using an
argument in the "Left" statement that I thought would indicate I only wanted
the first character. Apparently that was unnecessary. All works great!

Thanks again.