|
Dcounta
How do I count the total of repeating day that staff abset on the following
month? example : From 1 to 30 of every month Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a 1 1 1 staff b 1 1 1 1 1 staff c 1 1 1 1 Result shall be at following: Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 What's your recommended formula to the above result? |
Dcounta
Hi
I put my result matrix at A13:H22 and assumed the data was in A1:AE11 Change your locations and ranges to suit in cell B14 enter =SUMPRODUCT(($A$3:$A$11=$A14)*(B$2:AF$2=B$13)*B3:A F3) copy across through C14:H14, then copy B14:H14 down -- Regards Roger Govier "chau" wrote in message ... How do I count the total of repeating day that staff abset on the following month? example : From 1 to 30 of every month Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a 1 1 1 staff b 1 1 1 1 1 staff c 1 1 1 1 Result shall be at following: Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 What's your recommended formula to the above result? |
Dcounta
Dear Roger,
Instead of using number to count of day, can I use text to represent the number of day absent Example : Abbreviation MC(1.0) - Medical Leave (one day) EL(1.0) - Emergency Leave (one day) ML (1.0)- Materity Leave (one day) Just like following Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a MC(1.0) MC(1.0) MC(1.0) staff b MC(1.0) MC(1.0) EL.0) staff c EL(1.0) EL(1.0) Then, result shall be same as following Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 "Roger Govier" wrote: Hi I put my result matrix at A13:H22 and assumed the data was in A1:AE11 Change your locations and ranges to suit in cell B14 enter =SUMPRODUCT(($A$3:$A$11=$A14)*(B$2:AF$2=B$13)*B3:A F3) copy across through C14:H14, then copy B14:H14 down -- Regards Roger Govier "chau" wrote in message ... How do I count the total of repeating day that staff abset on the following month? example : From 1 to 30 of every month Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a 1 1 1 staff b 1 1 1 1 1 staff c 1 1 1 1 Result shall be at following: Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 What's your recommended formula to the above result? |
Dcounta
Hi
Without distinguishing between the different types of leave, then =SUMPRODUCT(($A$3:$A$11=$A14)*(B$2:AF$2=B$13)*(B3: AF3<"")) -- Regards Roger Govier "chau" wrote in message ... Dear Roger, Instead of using number to count of day, can I use text to represent the number of day absent Example : Abbreviation MC(1.0) - Medical Leave (one day) EL(1.0) - Emergency Leave (one day) ML (1.0)- Materity Leave (one day) Just like following Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a MC(1.0) MC(1.0) MC(1.0) staff b MC(1.0) MC(1.0) EL.0) staff c EL(1.0) EL(1.0) Then, result shall be same as following Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 "Roger Govier" wrote: Hi I put my result matrix at A13:H22 and assumed the data was in A1:AE11 Change your locations and ranges to suit in cell B14 enter =SUMPRODUCT(($A$3:$A$11=$A14)*(B$2:AF$2=B$13)*B3:A F3) copy across through C14:H14, then copy B14:H14 down -- Regards Roger Govier "chau" wrote in message ... How do I count the total of repeating day that staff abset on the following month? example : From 1 to 30 of every month Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a 1 1 1 staff b 1 1 1 1 1 staff c 1 1 1 1 Result shall be at following: Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 What's your recommended formula to the above result? |
Dcounta
Got it...thks a lot..
"chau" wrote: How do I count the total of repeating day that staff abset on the following month? example : From 1 to 30 of every month Mon Tue Wed Thu Fri Sat Sun Mon Tue We Thu Fri Sat Sun Mon & etc staff a 1 1 1 staff b 1 1 1 1 1 staff c 1 1 1 1 Result shall be at following: Mon Tues Wed Thu Fri Sat Sun Staff a 1 1 1 Staff b 1 2 1 What's your recommended formula to the above result? |
| All times are GMT +1. The time now is 04:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com