Variation on a SUMIF

[ChrisCarrot]

Does anyone know if it's possible to do something similar to a SUMIF, but on part of the Cell

For example, if I have a column of data, but each value is held in one cell. (eg ABC-1 is all in one cell).

ABC-1
ABC-2
CDE-3
ZTY-4
ABC-5

In the example above, i'd want to say "if the first three characters are "ABC", then add the last characters together. eg 1+2+5 = 8

I realise that i could do a text to columns to split across 2 columns then do a SUMIF, but trying to avoid that if possible.

Any advice would be very gratefully received. Thanks in advance.

Thank you

 

[steve the fish]

Try this:

=SUMPRODUCT(--(LEFT(A1:A5,3)="ABC"),--(MID(A1:A5,FIND("-",A1:A5)+1,99)))

 

[ChrisCarrot]

Wow! That was quick. I thought I was potentially asking the impossible.

I don't really understand how it works, but will try to get my head around it.

Thank you very much for such a quick response.

 

[vds1]

Another way when number of characters before "-" is uneven

=SUMPRODUCT(--(MID(A1:A5,1,FIND("-",A1:A5)-1)="ABC")*MID(A1:A5,FIND("-",A1:A5)+1,255))

 

[ISY]

Hi

=SUM(IF(LEFT(A1:A5,FIND("-",A1:A5)-1)=LEFT(A1,FIND("-",A1)-1),--RIGHT(A1:A5,LEN(A1:A5)-FIND("-",A1:A5)),0))

 

[ChrisCarrot]

Thank you for your help.

Just one further challenge..... if not all rows have data in, any idea how i could get it not to error on the total.

Eg

ABC-1
ABC-2
(intentional blank row)
CDE-3
ZTY-4
ABC-5

In the example above, i'd want to say "if the first three characters are "ABC", then add the last characters together. eg 1+2+5 = 8