0

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 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 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 at 3:25
0

Use REGEXMATCH :

=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 – James Denney Oct 15 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 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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