CountA or DcountA, that is the question??

N

Niqui

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
 
S

Sam Wilson

=SUM(1-ISERROR((FIND("W",A1:A14))))

but use with Ctrl + Shift + Enter after you've typed it in/pasted it rather
than just Enter. It should look like

{=SUM(1-ISERROR((FIND("D",A1:A20))))} if you do it correctly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top