Need help counting cells that contain any number but not exact string TR is in the cell

[RaffPost]

Hello all,

Thanks in advance for looking and hopefully you'll be able to help me.

I've created a restaurant work schedule and want to count how many staff members are scheduled. I only want to count cells that contain ANY number in them BUT NOT cells that contain any number plus the exact letters TR.

Example:

9
10
11
1130
12
1230
3
330
4
430
5
530
6
630
7

Basically it's schedule start times without the : between the hours and minutes. I may be adding other start times in the future so I would like to keep the formula as open as possible to not have any errors once I add another time slot.

How can I count the below range of cells to tell me that I have ONLY 5 people working and not count the 3 people who are Training?

9
9 TR
10
10 TR
1030
11
11 TR
12

Thanks!

 

[skywriter]

Maybe this.

Excel 2010
	A	B	C	D
1	9		Training	Not Training
2	9 TR		3	5
3	10
4	10 TR
5	1030
6	11
7	11 TR
8	12
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(NOT(ISNUMBER(A1:A8))))
D2	=SUMPRODUCT(--(ISNUMBER(A1:A8)))

 

[circledchicken]

Hi,

Another alternative to count just the number of cells that contain numbers might be something like:

=COUNT(A1:A8)

 

[skywriter]

Cross posted.

 

[RaffPost]

Hello all,

Thanks in advance for looking and hopefully you'll be able to help me.

I've created a restaurant work schedule and want to count how many staff members are scheduled. I only want to count cells that contain ANY number in them BUT NOT cells that contain any number plus the exact letters TR.

Example:

9
10
11
1130
12
1230
3
330
4
430
5
530
6
630
7

Basically it's schedule start times without the : between the hours and minutes. I may be adding other start times in the future so I would like to keep the formula as open as possible to not have any errors once I add another time slot.

How can I count the below range of cells to tell me that I have ONLY 5 people working and not count the 3 people who are Training?

9
9 TR
10
10 TR
1030
11
11 TR
12

Thanks!

I've also posted this on Excel Forum to get other responses.

Cell count for columns containing text cells for ANY numbers but NOT the letters TR

 

[RaffPost]

Maybe this.

Excel 2010

	A	B	C	D
1	9		Training	Not Training
2	9 TR		3	5
3	10
4	10 TR
5	1030
6	11
7	11 TR
8	12

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =SUMPRODUCT(--(NOT(ISNUMBER(A1:A8)))) D2 =SUMPRODUCT(--(ISNUMBER(A1:A8))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I should have mentioned that there will be some cell entries that are text based that contain other letters.

9
11 L
1130
12 TR
4
4 TR
5 CAN
6
6 TR

I don't think this will work. Any other thoughts on how to count these cells?

 

[skywriter]

This counts all cells that don't have TR in them, is that what you want.

Excel 2010
	A	B	C
1	9		6
2	11 L
3	1130
4	12 TR
5	4
6	4 TR
7	5 CAN
8	6
9	6 TR
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF(A1:A9,"<>*TR*")

 

[RaffPost]

This counts all cells that don't have TR in them, is that what you want.

Excel 2010

	A	B	C
1	9		6
2	11 L
3	1130
4	12 TR
5	4
6	4 TR
7	5 CAN
8	6
9	6 TR

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =COUNTIF(A1:A9,"<>*TR*") <tbody> </tbody>

<tbody>
</tbody>

Bruce - I appreciate all your responses but more importantly - your patience.

The range of cells may also contain some text only that are not numbers as well as blank cells. I will list all possible cell combinations below.

9
10
11
11 L
11 TR
(Blank Cell)
12
12 L
12 TR
OC
X

I will also have other columns with somewhat similar criteria - similar times with and without TR, but also times listed with other letters in the cell. Example below

4
5
5 BS
5 TR
6
6 BR
6 FR
6 TR
7
OC BR
OC BS
OC FR
X

Please keep in mind that other possible entries could come into play in the future - other start times as well as other letters could be used. I'm hoping for a formula that will also cover this and not have to be changed if I add other numbers and letters in the future.

Once again - I appreciate your help with this problem I am having and thanks for your patience.

 

[skywriter]

The best I can do is a custom function.
It's code, but it's very simple to use.
Once it's installed you use it like a lot of functions you are used to, such as Sum, Count etc.
The function is called CountCustom and in the example below you see that you type
=CountCustom( enter the cells) and press enter.

To install the code do the following.

Instructions for Installing Function Code
1) Make sure your worksheet is saved as .xlsm file (2007 and later versions).
2) Enable macros
3) Copy the code below, select the spreadsheet window and press Alt + F11, this will take you into the code editor.
4) Choose - Insert then Module from the menus along the top.
5) Paste the code where the cursor is flashing.
6) Close the window.
7) The function is now ready to be used.
8) In the cell you want to see the function results type
=CountCustom(select or type the cell reference you want in the function) hit enter.

Function CountCustom(Cells As Range) As Long
Dim tempArray, c As Long, r As Range

     For Each r In Cells
     If r.Value = Empty Then GoTo 1
     If IsNumeric(r.Value) Then
        c = c + 1
        GoTo 1
     End If
        If InStr(1, r.Value, " ") Then
            tempArray = Split(r.Value, " ")
            If IsNumeric(tempArray(0)) And Trim(tempArray(1)) <> "TR" Then c = c + 1
        End If
                    
1   Next r
CountCustom = c

End Function

You say you want to count the cells with times in them, but not those with TR, which I believe in the case below is 13, so you see the function working.
Excel 2010

	A	B	C
1			13
2	9
3	10
4	11
5	11 L
6	11 TR
7
8	12
9	12 L
10	12 TR
11	OC
12	X
13	4
14	5
15	5 BS
16	5 TR
17	6
18	6 BR
19	6 FR
20	6 TR
21	7
22	OC BR
23	OC BS
24	OC FR
25	X

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =CountCustom(A1:A25) <tbody> </tbody>

<tbody>
</tbody>

 

[RaffPost]

The best I can do is a custom function.
It's code, but it's very simple to use.
Once it's installed you use it like a lot of functions you are used to, such as Sum, Count etc.
The function is called CountCustom and in the example below you see that you type
=CountCustom( enter the cells) and press enter.

To install the code do the following.

Instructions for Installing Function Code
1) Make sure your worksheet is saved as .xlsm file (2007 and later versions).
2) Enable macros
3) Copy the code below, select the spreadsheet window and press Alt + F11, this will take you into the code editor.
4) Choose - Insert then Module from the menus along the top.
5) Paste the code where the cursor is flashing.
6) Close the window.
7) The function is now ready to be used.
8) In the cell you want to see the function results type
=CountCustom(select or type the cell reference you want in the function) hit enter.

Function CountCustom(Cells As Range) As Long
Dim tempArray, c As Long, r As Range

     For Each r In Cells
     If r.Value = Empty Then GoTo 1
     If IsNumeric(r.Value) Then
        c = c + 1
        GoTo 1
     End If
        If InStr(1, r.Value, " ") Then
            tempArray = Split(r.Value, " ")
            If IsNumeric(tempArray(0)) And Trim(tempArray(1)) <> "TR" Then c = c + 1
        End If
                    
1   Next r
CountCustom = c

End Function

You say you want to count the cells with times in them, but not those with TR, which I believe in the case below is 13, so you see the function working.
Excel 2010

	A	B	C
1			13
2	9
3	10
4	11
5	11 L
6	11 TR
7
8	12
9	12 L
10	12 TR
11	OC
12	X
13	4
14	5
15	5 BS
16	5 TR
17	6
18	6 BR
19	6 FR
20	6 TR
21	7
22	OC BR
23	OC BS
24	OC FR
25	X

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C1 =CountCustom(A1:A25) <tbody> </tbody>

<tbody>
</tbody>

I didn't work - the cell in which I listed the custom formula now displays the formula in it. No error messages or anything else.

I've gone over the instructions carefully and am certain I din't miss a step.