Sum, if a column CONTAINs certain word

Try

=SUMIF(B1:B100,"*ABC*",C1:C100)

Edit: =SUMIF(B1:B100,"*ABC*",C1:C100)

=SUMIF(B9:B1182,"*ABC*",C9:C182)

Try this:

Formula:

Please Login or Register  to view this content.

abousetta

Hi Candl66,

Welcome to the forum.

Try using below formula:-

Formula:

Please Login or Register  to view this content.

See attached:- sum if text present.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

you are welcome Candl66..

the part SEARCH("ABC",$B$2:$B$4) searches the ABC in B2:B4 and if found it gets the position number of ABC in that cell.. now ISNUMBER(SEARCH("ABC",$B$2:$B$4) checks if that is actually a number and where it gets a number that becomes True else all becomes False.. trues are actually 1s and Falses are 0s.. and then * signs ensures that the values from $C$2:$C$4 gets collected and multiplied previously colllected 1s and 0s.. so 0 multiplication results in 0 and 1 multiplication results in the number, which we are supposed to take sum of..at the end sum{ array } ensures collected numbers gets summed up to produce your desired result

I hope this is not much confusing

Regards,
DILIPandey

<click on below 'star' if this helps>

I would expect the SUMIF+wildcard approach to work, as suggested by abousetta and Fotis, are you sure that doesn't work? Can you post the exact formula you tried? If it really doesn't work that probably means that the "numbers" in the sum range are not real numbers but text strings....

...Fotis,
I tried that before, tested again. Gives me 0 as a result. Thank you very much!

No. Does not give 0. Gives the correct result. Abusseta's formula gives also correct result. And as daddylonglegs said:

...if it really doesn't work that probably means that the "numbers" in the sum range are not real numbers but text strings....

But you told us that

...if column B (alphanumeric) CONTAINS word "ABC", then ADD all corresponding values in column C into one 1 number and display.

It's ok to prefer an Array formula or anything you want but not because the other solutions don't work!

Some Excel functions may work differently or not at all in Google docs......but SUMIF works OK for me in Google docs as it does in excel. Are you saying that the formulas in Fotis' example give you different results?

Of course in 5 differents computer that i tested gives me the correct result. All three formulas give 4 as result.

In 99.9% of my formula suggestions, i test these at least one time before post. That i also did in this case.

Isue is closed for me.