Sum of Countifs: a simple question, I'm sure

[Nate Westcott]

I am trying to count the number of times a string appears in a column. Cells in that column have that string, and occasionally other data as well. I'm not sure why this isn't working for me:

=SUM(COUNTIF(Sheet2!G:G,{"Things"}))

This next formula is working for me, but only counts the string if it is the only thing in the cell.

=COUNTIF(Sheet2!G:G,"Things")

What formula should I use when I want to count a string whether it is the only thing in the cell or not? Thank you for any help you can offer, in advance.

[Jonmo1]

Try

=COUNTIF(Sheet2!G:G,"*Things*")

[Pete_UK]

If you use:

=COUNTIF(Sheet2!G:G,"Things")

then the count will only reflect exact matches. However, you can use the wildcard characters with COUNTIF, so you could have this instead:

=COUNTIF(Sheet2!G:G,"*Thing*")

and this would include in the count those cells which have "nothing", "some thing", "things", "farthing" and so on. You can leave one of the asterisks out if you just want to count cells that begin with that word, or those cells which end with that word.

Hope this helps.

Pete

[bebo021999]

Some ways of using COUNTIF:
=COUNTIF(A:A,"Adam") counts cells with only Adam in
=COUNTIF(A:A,"Adam*") counts cells with Adam1,Adam2,...
=COUNTIF(A:A,"*Adam*") counts cells with xAdam1,yAdam2,...
=COUNTIF(A:A,{"Adam","Eva"}) establishes array with either Adam or Eva, then SUM(COUNTIF(A:A,{"Adam","Eva"})) (Aray formula) counts cells with either Adam or Eva

I am not sure these match your issue or not?

[Nate Westcott]

Thank you, all. This fixed my problem. I had forgotten that you need two asterixes, not just one.

[Jonmo1]

Glad to help, thanks for the feedback.