Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default CountA or DcountA, that is the question??

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 523
Default CountA or DcountA, that is the question??

=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.



"Niqui" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
DCOUNTA question Barb Reinhardt Excel Worksheet Functions 0 January 14th 08 11:11 PM
Question Involving COUNTIF and Possibly DCOUNTA Piscator Excel Worksheet Functions 0 July 31st 07 06:34 PM
Question Involving COUNTIF and Possibly DCOUNTA PCLIVE Excel Worksheet Functions 0 July 31st 07 06:31 PM
Question Involving COUNTIF and Possibly DCOUNTA T. Valko Excel Worksheet Functions 0 July 31st 07 06:28 PM
DCOUNTA Complex Criteria Question Elliot Colbert Excel Worksheet Functions 5 June 19th 06 10:57 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"