Google Pattern Matching / RegEx for COUNTIF

Question

The sample matching documentation for Google Sheets did not help. I have been reading and searching for a long time and cannot find exactly this problem. It may be difficult for me to find the right search terms, but here is the problem:

I have several numbers (part numbers) that follow this format: ##-####

Categories can be identified by numbers 50-03## , that is, 50-03## will be one product category, and the remaining 2 digits are model specific.

I tried to run this:

 =countif(E9:E13,"50-03[123][012]*") 

( E9:E13 contains the part number formatted as text. If I format it in any other way, the values ​​will be corrupted because Google Sheets thinks I'm writing a date or trying to do arithmetic.)

This returns 0 every time if I shouldn't have changed to:

 =countif(E9:E13,"50-03*") 

So it seems like wildcards work, but pattern matching doesn't work?

Answer 1

As you determined, and Wiktor noted that COUNTIF only supports wildcards.

There are many ways to do what you want but name but 2

 =ArrayFormula(SUM(--REGEXMATCH(E9:E13, "50-03[123][012]*"))) =COUNTA(FILTER(E9:E13, REGEXMATCH(E9:E13, "50-03[123][012]*"))) 

Answer 2

Please want your help. I have a Google sheet.

when just click the button, I want to copy the value from the active cell sheet1 and paste the spreadsheets into the cell in sheet2. and go to sheet 2 by pressing the button