# Looking for a Formula.

#### LearnExcel&VBA

##### New Member
Hi All,

I am looking for a formula which counts the Employee leave throughout the complete years dates as available in attached sheet

I have created a formula but do not know how to add the Joining date available in cell B3 and Completion date available in cell C3 in the formula to count the employee leaves between the joining and completion dates. along with the condition if D2 = Probation then it should show the probation leaves if = Complete then it should show the leaves after completion date.

Any help would be highly appreciated.

#### Attachments

• 26.5 KB Views: 13

#### vletm

##### Excel Ninja
LearnExcel&VBA
You could find Use Relevant words in the Title and in the tag Box, This will aid future searches.
Your original Title would be something ... other.

#### LearnExcel&VBA

##### New Member
Hi, Sure i will go through let me know you further that i have created this one please look into it.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4

It is just for the month of Jan But i do not know how to add the Employee reference

#### bosco_yip

##### Excel Ninja
Hi, Sure i will go through let me know you further that i have created this one please look into it.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4

It is just for the month of Jan But i do not know how to add the Employee reference
Maybe,

Based on your formula, try this modified formula in E3 and copied down :

=IF(D3="Complete",0,COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"L",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)+COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"H",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)/2)+COUNTIFS(INDIRECT(TEXT(B3,"mmm!")&ROW(A2)&":"&ROW(A2)),"S",INDIRECT(TEXT(B3,"mmm")&"!1:1"),">="&B3,INDIRECT(TEXT(B3,"mmm")&"!1:1"),"<="&C3)/4

Regards

#### LearnExcel&VBA

##### New Member
bosco_yip

Hi, I hope you are doing well.

I've tried your formula it is working well but the formula is working for the month of "Jan" rather than all Jan-Dec.

If i use my formula that is short than yours for the month of Jan and add further Feb, Mar and so on. then it would become lengthy to adding other months in formula.

=IF(D3="Complete","",COUNTIFS(Jan!B2:AF2,"L",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)+COUNTIFS(Jan!B2:AF2,"H",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/2)+COUNTIFS(Jan!B2:AF2,"S",Jan!B1:AF1,">"&B3,Jan!B1:AF1,"<"&C3)/4&""&

IF(D3="Complete","",COUNTIFS(Feb!B2:AF2,"L",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)+COUNTIFS(Feb!B2:AF2,"H",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)/2)+COUNTIFS(Feb!B2:AF2,"S",Feb!B1:AF1,">"&B3,Feb!B1:AF1,"<"&C3)/4&""&

IF(D3="Complete","",COUNTIFS(Mar!B2:AF2,"L",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)+COUNTIFS(Mar!B2:AF2,"H",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)/2)+COUNTIFS(Mar!B2:AF2,"S",Mar!B1:AF1,">"&B3,Mar!B1:AF1,"<"&C3)/4

and further....

Looking for a short formula solution your formula is perfect if it covers the all month.

If you can create further short formula i would really appreciate the help.

Last edited:

#### Excel Wizard

##### Member
Maybe try

=IF(D3="Complete","",SUMPRODUCT(COUNTIFS(INDIRECT(TEXT(EDATE(360,ROW(\$A\$1:\$A\$12)),"[\$-en]mmm")&"!A1:AF1"),">="&B3,INDIRECT(TEXT(EDATE(360,ROW(\$A\$1:\$A\$12)),"[\$-en]mmm")&"!A1:AF1"),"<="&C3,
INDIRECT(TEXT(EDATE(360,ROW(\$A\$1:\$A\$12)),"[\$-en]mmm")&"!A"&ROW(A2)&":AF"&ROW(A2)),{"L","H","S"})/{1,2,4}))

#### LearnExcel&VBA

##### New Member
Thank you so much for the help

#### XOR LX

##### Active Member
Hi,

Non-volatile alternative:

=IF(D3="Complete","",SUMPRODUCT((MMULT(N(SIGN(FILTERXML("<a><b>"&TEXTJOIN("0</b><b>",0,Jan:Dec!B\$1:AF\$1)&"0</b></a>","//b")/10-B3:C3)<>{-1,1}),{1;1})=2)*(MID(FILTERXML("<a><b>"&"ζ"&TEXTJOIN("ζ</b><b>ζ",0,Jan:Dec!B2:AF2)&"ζ</b></a>","//b"),2,1)={"L","H","S"})/{1,2,4}))

Regards