Shazir
Banned - Rules violations
- Joined
- Jul 28, 2020
- Messages
- 94
- Office Version
- 365
- Platform
- Windows
Trying to shorten this formula can anyone please help.
Formula is being used to get the leaves according to the dates if i Choose the date between 1-1-2020 to 31-1-2020 then i would have leave record of 1 month.
Please suggest a solution for this.
=COUNTIFS(Jan!C3:AG3,"L",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)+COUNTIFS(Jan!C3:AG3,"H",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Jan!C3:AG3,"S",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Feb!C3:AE3,"L",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)+COUNTIFS(Feb!C3:AE3,"H",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)/2+COUNTIFS(Feb!C3:AE3,"S",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)/4+COUNTIFS(Mar!C3:AG3,"L",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)+COUNTIFS(Mar!C3:AG3,"H",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Mar!C3:AG3,"S",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Apr!C3:AF3,"L",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)+COUNTIFS(Apr!C3:AF3,"H",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Apr!C3:AF3,"S",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(May!C3:AG3,"L",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)+COUNTIFS(May!C3:AG3,"H",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(May!C3:AG3,"S",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Jun!C3:AF3,"L",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)+COUNTIFS(Jun!C3:AF3,"H",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Jun!C3:AF3,"S",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Jul!C3:AG3,"L",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)+COUNTIFS(Jul!C3:AG3,"H",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Jul!C3:AG3,"S",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Aug!C3:AG3,"L",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)+COUNTIFS(Aug!C3:AG3,"H",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Aug!C3:AG3,"S",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Sep!C3:AF3,"L",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)+COUNTIFS(Sep!C3:AF3,"H",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Sep!C3:AF3,"S",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Oct!C3:AG3,"L",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)+COUNTIFS(Oct!C3:AG3,"H",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Oct!C3:AG3,"S",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Nov!C3:AF3,"L",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)+COUNTIFS(Nov!C3:AF3,"H",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Nov!C3:AF3,"S",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Dec!C3:AG3,"L",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)+COUNTIFS(Dec!C3:AG3,"H",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Dec!C3:AG3,"S",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)/4
Formula is being used to get the leaves according to the dates if i Choose the date between 1-1-2020 to 31-1-2020 then i would have leave record of 1 month.
Please suggest a solution for this.
=COUNTIFS(Jan!C3:AG3,"L",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)+COUNTIFS(Jan!C3:AG3,"H",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Jan!C3:AG3,"S",Jan!$C$1:$AG$1,">"&D3,Jan!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Feb!C3:AE3,"L",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)+COUNTIFS(Feb!C3:AE3,"H",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)/2+COUNTIFS(Feb!C3:AE3,"S",Feb!$C$1:$AE$1,">"&D3,Feb!$C$1:$AE$1,"<"&E3)/4+COUNTIFS(Mar!C3:AG3,"L",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)+COUNTIFS(Mar!C3:AG3,"H",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Mar!C3:AG3,"S",Mar!$C$1:$AG$1,">"&D3,Mar!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Apr!C3:AF3,"L",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)+COUNTIFS(Apr!C3:AF3,"H",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Apr!C3:AF3,"S",Apr!$C$1:$AF$1,">"&D3,Apr!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(May!C3:AG3,"L",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)+COUNTIFS(May!C3:AG3,"H",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(May!C3:AG3,"S",May!$C$1:$AG$1,">"&D3,May!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Jun!C3:AF3,"L",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)+COUNTIFS(Jun!C3:AF3,"H",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Jun!C3:AF3,"S",Jun!$C$1:$AF$1,">"&D3,Jun!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Jul!C3:AG3,"L",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)+COUNTIFS(Jul!C3:AG3,"H",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Jul!C3:AG3,"S",Jul!$C$1:$AG$1,">"&D3,Jul!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Aug!C3:AG3,"L",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)+COUNTIFS(Aug!C3:AG3,"H",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Aug!C3:AG3,"S",Aug!$C$1:$AG$1,">"&D3,Aug!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Sep!C3:AF3,"L",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)+COUNTIFS(Sep!C3:AF3,"H",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Sep!C3:AF3,"S",Sep!$C$1:$AF$1,">"&D3,Sep!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Oct!C3:AG3,"L",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)+COUNTIFS(Oct!C3:AG3,"H",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Oct!C3:AG3,"S",Oct!$C$1:$AG$1,">"&D3,Oct!$C$1:$AG$1,"<"&E3)/4+COUNTIFS(Nov!C3:AF3,"L",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)+COUNTIFS(Nov!C3:AF3,"H",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)/2+COUNTIFS(Nov!C3:AF3,"S",Nov!$C$1:$AF$1,">"&D3,Nov!$C$1:$AF$1,"<"&E3)/4+COUNTIFS(Dec!C3:AG3,"L",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)+COUNTIFS(Dec!C3:AG3,"H",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)/2+COUNTIFS(Dec!C3:AG3,"S",Dec!$C$1:$AG$1,">"&D3,Dec!$C$1:$AG$1,"<"&E3)/4