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

R

RaffPost

New Member
Joined
Mar 15, 2014
Messages
13
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Maybe this. :)


Excel 2010
ABCD
19TrainingNot Training
29 TR35
310
410 TR
51030
611
711 TR
812
Sheet1
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(NOT(ISNUMBER(A1:A8))))
D2=SUMPRODUCT(--(ISNUMBER(A1:A8)))
 
Last edited:
Upvote 0
Hi,

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

Code: 
=COUNT(A1:A8)
 
Upvote 0
RaffPost said:
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!
Click to expand...

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
 
Upvote 0
skywriter said:
Maybe this. :)

Excel 2010
ABCD

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Training[/TD]
[TD="align: center"]Not Training[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]9 TR[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]10 TR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1030[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]11 TR[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUMPRODUCT(--(NOT(ISNUMBER(A1:A8))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--(ISNUMBER(A1:A8)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
This counts all cells that don't have TR in them, is that what you want. :)


Excel 2010
ABC
196
211 L
31130
412 TR
54
64 TR
75 CAN
86
96 TR
Sheet1
Cell Formulas
RangeFormula
C1=COUNTIF(A1:A9,"<>*TR*")
 
Upvote 0
skywriter said:
This counts all cells that don't have TR in them, is that what you want. :)

Excel 2010
ABC

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]11 L[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]12 TR[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4 TR[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]5 CAN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]6 TR[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=COUNTIF(A1:A9,"<>*TR*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Last edited:
Upvote 0
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.

Code: 
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
ABC
11 L
11 TR
12 L
12 TR
OC
X
5 BS
5 TR
6 BR
6 FR
6 TR
OC BR
OC BS
OC FR
X

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=CountCustom(A1:A25)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
skywriter said:
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.

Code: 
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
ABC
11 L
11 TR
12 L
12 TR
OC
X
5 BS
5 TR
6 BR
6 FR
6 TR
OC BR
OC BS
OC FR
X

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=CountCustom(A1:A25)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
595
felixstraube
F
C
  • Solved
Counting commas between multiple specific strings of text in a single cell
2
Replies
10
Views
376
Country_Calc_2
C
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
801
Alex Blakenburg
A
S
  • Solved
Count of active employees between dates.
Replies
3
Views
631
Fluff
Fluff
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
437
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top