0

I would like to use this formula to check column A with below names:

SUM(COUNTIFS([Consolidate.xls]RawData!$A:$A,{"*Kobe Bryant*","*John Legend*","*Tom Kitten*","*Jason Kidd*","*Michael Jordan*"}))

Consolidate.xls, Row A:

     A
1  Kobe Bryant; Tom Kitten
2  Michael Jordan

As the result I will get 3. How can I ignore Tom Kitten in row 1? (I just need the first person to be counted and let me get the result =2.)

Improve this question
1
  • 2
    I guess you know, how the asterisk work? It's a wildchard character meaning it can be anything before and after the word. So if you could get rid of them, that would be the easiest.
    – leun4m
    Mar 8, 2018 at 9:48

1 Answer 1

Reset to default
1

You can do that by removing the wildcard (*) before each name. That way, the name will only be counted if it appears at the beginning of the cell value.

SUM(COUNTIFS([Consolidate.xls]RawData!$A:$A,{"Kobe Bryant*","John Legend*","Tom Kitten*","Jason Kidd*","Michael Jordan*"}))

The above equation would give the desired result, based on your data:

2

Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .