excel - Getting the sum of second column where column A contains specific characters but exclude a certain letter

Question

I have this setup in my worksheet

First     Second
ABXX      1
ABXX      2
ABCXX     10
ABXX      3
ABCXX     20

where AB and ABC are static and the XX are numbers

---

and i need to do the following in a single cell (cell 1)

If column 1 contains AB, get the sum of its 2nd column

---

and do this on another cell (cell 2)

if column 1 contains ABC, get the sum of its 2nd column

---

im using this formula =SUMIF(A:A,"*AB*",B:B)

but the problem is it also includes ABC because it also contains the letters "AB"

is there a way to go around this?

标签： excelexcel-formula

Answer 1

如果末尾总是有两位数字，那么也许只是?用来匹配单个字符：

=SUMIF(A:A,"AB??",B:B)

和

=SUMIF(A:A,"ABC??",B:B)