Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DCOUNTA question | Excel Worksheet Functions | |||
Question Involving COUNTIF and Possibly DCOUNTA | Excel Worksheet Functions | |||
Question Involving COUNTIF and Possibly DCOUNTA | Excel Worksheet Functions | |||
Question Involving COUNTIF and Possibly DCOUNTA | Excel Worksheet Functions | |||
DCOUNTA Complex Criteria Question | Excel Worksheet Functions |