Dcounta

[chau]

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?

 

[Roger Govier]

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:AF3)
copy across through C14:H14, then copy B14:H14 down

 

[chau]

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]

Hi

Without distinguishing between the different types of leave, then
=SUMPRODUCT(($A$3:$A$11=$A14)*(B$2:AF$2=B$13)*(B3:AF3<>""))

 

[chau]

Got it...thks a lot..