Date array count calculation in a month

[sachinviki]

Hi Experts,

I have a request here, I have employee attendance who logs their presence and absence, we want to monitor their weekly availability of each individual, based on which the jobs will be allocated for them. Meantime we are bringing this data to the dashboard. what i need is to calculate the availability dynamically for each month

October	01-10-2021	02-10-2021	03-10-2021	04-10-2021	05-10-2021	06-10-2021	07-10-2021	08-10-2021	09-10-2021	10-10-2021
AAAAAAAAA	B	WO	WO	B	B	B	B	B	WO	WO
BBBBBBBBBB	BL	WO	WO	B	B	B	B	V	WO	WO
CCCCCCCCC	B	WO	WO	B	B	B	B	B	WO	WO
DDDDDDDD	B	WO	WO	B	B	B	B	B	WO	WO
EEEEEEEEEEE	B	WO	WO	SL	SL	B	B	B	WO	WO
FFFFFFFFFFF	SL	WO	WO	SL	SL	SL	SL	SL	WO	WO
GGGGGGGG	B	WO	WO	A	A	A	A	A	WO	WO
HHHHHHHH	A	WO	WO	B	B	B	B	B	WO	WO

So, above is the table and "A" is the general shift, "B"is the second shift. I want to count both "A&B" for each week, Eg: 1st Oct 2021 to 3rd Oct 2021 and 4th Oct 2021 to 8th Oct 2021.

=COUNTIFS($B$2:$J$9,">=04-10-2021,$B$2:$J$9"&,"<="&08-10-2021,B4:AF9,"B",A:A,"ÄAAAAAAAA")

The above formula isn't working kindly help!

Regards,
Vignesh

 

[Saba Sabaratnam]

Hi Vignesh

You could use the following formula in A14.

=SUMPRODUCT(--(IF((IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,"")="A")+(IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,"")="B"),IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,0),"")<>""))

Kind regards

Saba

 

[sachinviki]

Hi Vignesh

You could use the following formula in A14.

=SUMPRODUCT(--(IF((IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,"")="A")+(IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,"")="B"),IF((B1:K1>=A12)*(B1:K1<=A13),B2:K9,0),"")<>""))

View attachment 48845

Kind regards

Saba

Hi Saba,

Thanks for the response and the solution, but i forgot to provide one more condition in that i.e. for the Person "ÄAAAAAA" what is the count in the specific range. Can you help me to complete the formula?

 

[Saba Sabaratnam]

You could use the following formula in B16 and copy it down.

=SUMPRODUCT(--(IF((IF(($B$1:$K$1>=$A$12)*($B$1:$K$1<=$A$13)*($A$2:$A$9=A16),$B$2:$K$9,0)="A")+(IF(($B$1:$K$1>=$A$12)*($B$1:$K$1<=$A$13)*($A$2:$A$9=A16),$B$2:$K$9,0)="B"),IF(($B$1:$K$1>=$A$12)*($B$1:$K$1<=$A$13)*($A$2:$A$9=A16),$B$2:$K$9,0)<>"")))


Can you please update our Excel version so that we can provide right solution for your Excel problem?

Kind regards

Saba