Cell count for columns containing any combination of ceretain numbers but NOT letters TR

Hello all,

Thanks in advance for looking.

I am creating a weekly restaurant staff schedule and could use some help. I am tying to calculate how many staff members are scheduled within a column based on a time that they have been scheduled to work in a cell.

The times listed would vary in the column. Times would be listed as a one, two, three or four digits as well as one to three letters at the end. Hours would be either single or double digits depending on the time of day.
Range of times would be the following:

9
9 TR
930
10
10 TR
11
11 L
11 TR
1130
12
12 L
12 TR

Count Numbers.jpg

I would like to not have to change the formula in the future if I decide to add other scheduled times to the schedule.

I was thinking of a formula that would read only the text containing numbers that DON'T have a TR in it.

What would the best formula to use to calculate the total number of staff working on that shift who have a scheduled time that doesn't include TR in the cells?

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

See if this works for you. In Cell C110 put in your codes:
Data Range

	C
110	9:00
111	9:00TR
112	10:00
113	10:00TR
114	11:00
115	11:00TR
116	11:00FR
117	12:00
118	12:00FR
119	12:30FR
120	OC
121	RO
122	X
123	X X X X X

In D110 type and copy down and over as required: =COUNTIF($D$20:$D$99,C$110)

Originally Posted by alansidman

See if this works for you. In Cell C110 put in your codes:
Data Range

	C
110	9:00
111	9:00TR
112	10:00
113	10:00TR
114	11:00
115	11:00TR
116	11:00FR
117	12:00
118	12:00FR
119	12:30FR
120	OC
121	RO
122	X
123	X X X X X

In D110 type and copy down and over as required: =COUNTIF($D$20:$D$99,C$110)

Thanks for the reply - I realize now that the workbook I uploaded had a few other working pages in there. I've removed the pages and changed some of the data. I'm uploading the workbook again - I'm not certain I know what to do based on your instructions.

I need the total count in a cell that tells me how many people I have scheduled to work that shift that are not training.

Adam 9
Danny 10
Emily 1030
Erica 11 TR
Gene 12
Lindsay 12 TR

This should tell me that I have 4 people working despite 6 people on that scheduled shift - two of which are training.

Thanks again!

[Template] Terroni Adelaide FOH Schedule.xlsx

This thread has been closed at the OP request. OP has started a new thread with appropriate correct data.