Counting text problem - formula works initially

[Anne P]

Hi!

I'll start off saying that I use excel just for the basics and don't have a
strong background in it. Here is my problem. I use a spreadsheet to put our
Dept schedule on and I need it to count the number of people for each part of
the shift, so we know that we have appropriate coverage.

So far, I have 3 rows counting the following
Row 1 - counts the night shift person staying 1st 1/2 of days
Row 2 - counts actual Day shift staff
Row 3 - counts the PM shift person coming in for 2nd 1/2 of days

I use the following text for each person, depending on shift and length of
shift (I work in Health Care)

N 10 Stays till 0900 on the day shift
D Day shift staff (0630 - 1530)
D 12 Stays till 1900
S 12 Comes in at 1030 on the day shift
S PM shift staff (1430 - 2300)
N 12 Comes in at 1830 on the PM shift
N Night shift staff (2230 0 0700)


The formulas I have are as follows i.e. 1st column
=COUNTIF(B7:B61,"*N10*")
=COUNTIF(B7:B61,"*D*")
=COUNTIF(B7:B61,"*S 12*")

This works just fine. It is not till I go down to row 20, starting to list
the PM shift. I use the same formulas as above , replacing the "*" with D 12,
S and N 12. At this point, it won't recognize ANYTHING in the columns!!!

My ranges/columns for counting are B7:B61 to AQ7:AQ61

What am I doing wrong???????

 

[Eduardo]

Hi
The problem I can see is an space between the letter and the number try
=COUNTIF(B7:B61,"*S12*")

 

[Shane Devenshire]

Hi,

I see nothing wrong, but you should show us the data on line 20 and beyond.

Note also you can simplify all your formuas to

=COUNTIF(B7:B61,"*N*")
=COUNTIF(B7:B61,"*D*")
=COUNTIF(B7:B61,"*S*")

or even

=COUNTIF(B7:B61,"N*")
=COUNTIF(B7:B61,"D*")
=COUNTIF(B7:B61,"S*")

 

[Anne P]

I have to keep the D12, N12, S12, N10, etc. It is the CODE that tells the
therapist at what time to come in and how long they are working.

N10 comes in at 2230 and works till 0900 (10 hr Night shift)
D12 comes in at 0630 and works till 1900 (12 hr Day shift)
S12 comes in at 1030 and works till 2300 (12 hr PM shift)
N12 comes in at 1830 and works till 0700 (12 hr Night Shift)
D comes in at 0630 and works till 1500 (traditional 8hr shift)
S comes in at 1430 and works till 2300 " " "
N comes in at 2230 and works till 0700 " " "


So if you can envision this as a HUGE 6 week calendar, I have rows 1,2,3
searching for the criteria for the day shift

Then for example, on row 20, 21 & 22 I am searching for the criteria for the
PM shift, and rows 40, 41, 42 searching for Night shift criteria.

See.. the problem is we have staff that crosses over and though hired for
Days, PMs or Nights, they can work other shifts. Confusing??? Hence the need
to have the spreadsheet counting for me!!


If I could send a copy of this thing, or get it online, it might make more
sense!!