Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l;
bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Do the columns represent days of the week? If so, are there column headers
that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
If this helps what I mean the headers are not actual, just for example. This
is how the first row of the list looks. A B C D E F G H AM AN ... 1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed Thu Fri Sat Sun 2 sick late sick dom 2 0 0 0 0 0 1 3 sick sick late sick 1 2 0 0 0 0 0 At AM2 I put this formula that counts only Monday inputs: =SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev")) I don't count "Late" and "early" is there a simple formula to be put in AM2? I don't know how else to explain this matter. Sorry to trouble you. Regards "Bojan" wrote: Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
I can't make out the layout you posted. They usually get "shredded" by the
line/word wraps. If your column headers are *text* entries Mon, Tue, Wed, etc: =SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0)))) -- Biff Microsoft Excel MVP "Bojan" wrote in message ... If this helps what I mean the headers are not actual, just for example. This is how the first row of the list looks. A B C D E F G H AM AN ... 1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed Thu Fri Sat Sun 2 sick late sick dom 2 0 0 0 0 0 1 3 sick sick late sick 1 2 0 0 0 0 0 At AM2 I put this formula that counts only Monday inputs: =SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev")) I don't count "Late" and "early" is there a simple formula to be put in AM2? I don't know how else to explain this matter. Sorry to trouble you. Regards "Bojan" wrote: Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
I tried your formula but I'm lost, Sorry for the trouble, I'll just use the
old formula. cheers "T. Valko" wrote: I can't make out the layout you posted. They usually get "shredded" by the line/word wraps. If your column headers are *text* entries Mon, Tue, Wed, etc: =SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0)))) -- Biff Microsoft Excel MVP "Bojan" wrote in message ... If this helps what I mean the headers are not actual, just for example. This is how the first row of the list looks. A B C D E F G H AM AN ... 1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed Thu Fri Sat Sun 2 sick late sick dom 2 0 0 0 0 0 1 3 sick sick late sick 1 2 0 0 0 0 0 At AM2 I put this formula that counts only Monday inputs: =SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev")) I don't count "Late" and "early" is there a simple formula to be put in AM2? I don't know how else to explain this matter. Sorry to trouble you. Regards "Bojan" wrote: Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Here's a small sample file that demonstrates this:
xBojan.xls 14kb http://cjoint.com/?jshE0GTLCg -- Biff Microsoft Excel MVP "Bojan" wrote in message ... I tried your formula but I'm lost, Sorry for the trouble, I'll just use the old formula. cheers "T. Valko" wrote: I can't make out the layout you posted. They usually get "shredded" by the line/word wraps. If your column headers are *text* entries Mon, Tue, Wed, etc: =SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0)))) -- Biff Microsoft Excel MVP "Bojan" wrote in message ... If this helps what I mean the headers are not actual, just for example. This is how the first row of the list looks. A B C D E F G H AM AN ... 1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed Thu Fri Sat Sun 2 sick late sick dom 2 0 0 0 0 0 1 3 sick sick late sick 1 2 0 0 0 0 0 At AM2 I put this formula that counts only Monday inputs: =SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev")) I don't count "Late" and "early" is there a simple formula to be put in AM2? I don't know how else to explain this matter. Sorry to trouble you. Regards "Bojan" wrote: Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Thank you Valko, that's what I needed, how can I learn more about this type
of formula? Regards "T. Valko" wrote: Here's a small sample file that demonstrates this: xBojan.xls 14kb http://cjoint.com/?jshE0GTLCg -- Biff Microsoft Excel MVP "Bojan" wrote in message ... I tried your formula but I'm lost, Sorry for the trouble, I'll just use the old formula. cheers "T. Valko" wrote: I can't make out the layout you posted. They usually get "shredded" by the line/word wraps. If your column headers are *text* entries Mon, Tue, Wed, etc: =SUMPRODUCT(--(A1:AD1="mon"),--(ISNUMBER(MATCH(A2:AD2,{"a/l","dom","sick","bev"},0)))) -- Biff Microsoft Excel MVP "Bojan" wrote in message ... If this helps what I mean the headers are not actual, just for example. This is how the first row of the list looks. A B C D E F G H AM AN ... 1 Mon Tue Wed Thu Fri Sat Sun Mon Tue ....(31 days). Mon Tue Wed Thu Fri Sat Sun 2 sick late sick dom 2 0 0 0 0 0 1 3 sick sick late sick 1 2 0 0 0 0 0 At AM2 I put this formula that counts only Monday inputs: =SUM(COUNTIF(A2,"a/l"),COUNTIF(A2,"dom"),COUNTIF(A2,"sick"),COUNTIF(A 2,"bev"),COUNTIF(G2,"sick"),COUNTIF(G2,"bev"),COUN TIF(G2,"dom"),COUNTIF(G2,"a/l"),COUNTIF(P2,"sick"),COUNTIF(P2,"bev"),COUNTIF(P 2,"dom"),COUNTIF(P2,"a/l"),COUNTIF(W2,"bev"),COUNTIF(W2,"dom"),COUNTIF(W2 ,"sick"),COUNTIF(W2,"a/l"),COUNTIF(AD2,"a/l"),COUNTIF(AD2,"dom"),COUNTIF(AD2,"sick"),COUNTIF (AD2,"bev")) I don't count "Late" and "early" is there a simple formula to be put in AM2? I don't know how else to explain this matter. Sorry to trouble you. Regards "Bojan" wrote: Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Me again,
Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Using the formula from the sample file:
=SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0)))) As you copy down the reference to A1:O1 will remain constant while the reference to A2:O2 will increment as desired. -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Me again, Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
Thank you very Much Valko, it works!
Best Regards "T. Valko" wrote: Using the formula from the sample file: =SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0)))) As you copy down the reference to A1:O1 will remain constant while the reference to A2:O2 will increment as desired. -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Me again, Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bojan" wrote in message ... Thank you very Much Valko, it works! Best Regards "T. Valko" wrote: Using the formula from the sample file: =SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0)))) As you copy down the reference to A1:O1 will remain constant while the reference to A2:O2 will increment as desired. -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Me again, Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
you could shave a few keystrokes
=SUMPRODUCT((A$1:O$1="mon")*(A2:O2={"a/l";"dom";"sick";"bev"})) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Thank you very Much Valko, it works! Best Regards "T. Valko" wrote: Using the formula from the sample file: =SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0)))) As you copy down the reference to A1:O1 will remain constant while the reference to A2:O2 will increment as desired. -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Me again, Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with Absence sheet formula.
It's a trade-off.
That formula is a few keystrokes shorter but the other formula has a very slight efficiency advantage. -- Biff Microsoft Excel MVP "JMB" wrote in message ... you could shave a few keystrokes =SUMPRODUCT((A$1:O$1="mon")*(A2:O2={"a/l";"dom";"sick";"bev"})) "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Thank you very Much Valko, it works! Best Regards "T. Valko" wrote: Using the formula from the sample file: =SUMPRODUCT(--(A$1:O$1="mon"),--(ISNUMBER(MATCH(A2:O2,{"a/l","dom","sick","bev"},0)))) As you copy down the reference to A1:O1 will remain constant while the reference to A2:O2 will increment as desired. -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Me again, Thank you for your help, I have another question: Is there a way to lock this "H80:AL80" part of the formula that tests the days/dates so when I copy the formula to the following name in the list thay can remain to count just that row-just the dates. Example, Let's say I have 10 people on my absence list and one row with the days or dates, If I copy the formula downwards, it will go H81:AL81, H82:AL82 etc... and I have to manually adjust each formula to be H80:AL80. Thank you "T. Valko" wrote: Do the columns represent days of the week? If so, are there column headers that are the *dates* for the month? If that's the case then you can use a formula that tests the header row for the day of the week and test the data row for those specific criteria: H80:AL80 = *dates* for the month H81:AL81 = data To count instances of a/l, dom, sick, and bev for Mondays: =SUMPRODUCT(--(WEEKDAY(H80:AL80,2)=1),--(ISNUMBER(MATCH(H81:AL81,{"a/l","dom","sick","bev"},0)))) For the other weekdays, in the WEEKDAY function change =n to: For Monday: =1 For Tuesday: =2 For Wednesday: =3 For Thursday: =4 For Friday: =5 For Saturday: =6 For Sunday: =7 -- Biff Microsoft Excel MVP "Bojan" wrote in message ... Hi, I manage a monthly absence sheet, we have 4 types of absences "sick; a/l; bev; dom" and "late or Early finish" All of these types of absence are entered in one row for a certain person on a certain date. (calendar type of row,31 cells ). At the end of that row I need to calculate and divide them monthly for each day of the week how many absences were there(Mon2; Tue-o; Wed-3 etc...) but excluding the "Late" or "early" inputs. I do have a formula and I thought if there is an simple one that I can use? Here is a copy of the formula I use for each day in the week, this is for Monday only: =SUM(COUNTIF(H81,"a/l"),COUNTIF(H81,"dom"),COUNTIF(H81,"sick"),COUNTIF (H81,"bev"),COUNTIF(O81,"sick"),COUNTIF(O81,"bev") ,COUNTIF(O81,"dom"),COUNTIF(O81,"a/l"),COUNTIF(V81,"sick"),COUNTIF(V81,"bev"),COUNTIF (V81,"dom"),COUNTIF(V81,"a/l"),COUNTIF(AC81,"bev"),COUNTIF(AC81,"dom"),COUNTI F(AC81,"sick"),COUNTIF(AC81,"a/l"),COUNTIF(AJ81,"a/l"),COUNTIF(AJ81,"dom"),COUNTIF(AJ81,"sick"),COUNT IF(AJ81,"bev")) thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace numbers to 1s and 0s for presence/absence | Excel Discussion (Misc queries) | |||
Employee Absence Schedule for 2007? | Excel Discussion (Misc queries) | |||
Employee Absence Schedule Template | Excel Discussion (Misc queries) | |||
HOW DO I CHANGE ABSENCE TEMPLATE 2005, TO 2006 | Excel Worksheet Functions | |||
how do I do a day to day holiday and absence chart | Setting up and Configuration of Excel |