Hello,

i am hoping someone can help, these forums are awesome for solving problems!

To avoid anyone getting the worng idea, or having to ask a million questions
based on my spreedsheet i will be detailed.

I have a spreedsheet for my 400 casual medical staff, they let me know when
they can work, and this is recorded as a "M" for Morning, "A for Afternoon,
"N" for Night, "D12" for a 12 hour day and "N12" for a 12 hour Night. If they
are placed in a dept or Ward I then record in the same cell where they went,
e.g a morning shift in wd 3 would be recorded as MW3. this is where it gets
tricky. Not everyone gets a shift and i want to keep track of how many shifts
each person gets in a fortnight, so how do I sum only the shifts worked?

I have tried using a color code to represent the various shifts and making
the cell red if they are available for a "M" and blue if for a "A", then if
they got a shift recorded where they went , and then used =countA(A1:A14) to
count only the shifts worked, this worked perfectly, but my colleges who also
share my workbook and place staff hate this and want to go back to using
M,A,N,D12 and N12, and not cell colors.

So how do i count only the shifts worked and ignore the shift not worked,
without having to go and first deleted the cells value if they didnt get a
shift?

Can i use =DcountA and ask it to not look for a specific letter, rather than
only count a specific letter, or is there another function completely??

any help greatly appreciated