In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only Bud Light from the following:

Bud Light
Bud Light Platinum
Bud Light Lime
Bud Light NFL

Carried out over 100 times at random.

I am trying to dig out what signage and promotions cost.

Link to Google Sheet

Edited link hopefully it is useful now.

The above sheet is a sample of what I am trying to do.

The reasoning for this is to return how much we spend on signage from a particular printer based on Brand of Beer and the type of sign it is. There may be 1000 orders in a months time and the report generated by the system lumps everything together, thus the need to dig it all out and the randomness of it all.

  • Please share your spreadsheet (without private or confidential data). At present, your link requires a request for access; the sheet should be accessible without requesting permission.
    – Tedinoz
    Oct 15, 2019 at 3:13
  • Would you please provide more information about "why" you need to find the value - this may influence the method employed. You also mention "carried out 100 times at random" - please explain the reason and timing for this.
    – Tedinoz
    Oct 15, 2019 at 3:19
  • Have you thought of using a "simple" filter on the "Name" column of the relevant sheet; that allows you to select the specific name, and the results in each column only for the name that you selected in the "Name" column.
    – Tedinoz
    Oct 15, 2019 at 3:25

2 Answers 2



=countif(ArrayFormula(regexmatch(A1:A10,"^Bud Light$")),true)

This formula counts the cells who match the regex ^Bud Light$ : Match exactly Bud Light

You can add lower() and proper()to clean the range you want to test.

  • Would this work in reverse if I need to find just Bud? Thanks Oct 15, 2019 at 20:24
  • Replace the regex with ^Bud$ to find cells with the exact string Bud alone but not Bud light for example. The operators ^ and $ are for start and end of the string. If you want to detect cells with Bud + anything just use the regex Bud without any operator. Reference
    – pjmg
    Oct 15, 2019 at 21:44

You can also use a cell (B1) for the input value to compare with. This example will match if A1:A contains the value in B1.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.