# shorten the formula

#### Shazir

##### Board Regular

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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Eric W

##### MrExcel MVP
Find a column on your workbook with the names of the other sheets, Jan, Feb, Mar, etc. In this formula I put the names in B3:B14, but you can change that. Then try:

Excel Formula:
``=SUMPRODUCT(COUNTIFS(INDIRECT(\$B\$3:\$B\$14&"!"&CELL("address",C3)&":"&CELL("address",AG3)),{"L","H","S"},INDIRECT(\$B\$3:\$B\$14&"!C1:AG1"),">"&D3,INDIRECT(\$B\$3:\$B\$14&"!C1:AG1"),"<"&E3)/{1,2,4})``

#### Shazir

##### Board Regular
Thank you for putting the efforts.
Can you please convert this line into your formula i have tried but its not working

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

Tried with

#### Eric W

##### MrExcel MVP
If you just want the counts from the Jan sheet, you can do without all the INDIRECTs:

=SUM(COUNTIFS(Jan!C3:AG3,{"L","H","S"},Jan!\$C\$1:\$AG\$1,">"&D3,Jan!\$C\$1:\$AG\$1,"<"&E3)/{1,2,4})

#### mikerickson

##### MrExcel MVP
In the OP I see a huge, un-editable formula. To make it more useable, I would recommend that you use helper columns, with reasonable sized formulas, to get intermediate results and then apply other reasonable sized formulas to the results in those helper columns.

#### Shazir

##### Board Regular
Eric W

Sir Thank you so much its working.

mikerickson

Thank you Sir for the guidance.

Replies
0
Views
73
Replies
2
Views
38
Replies
10
Views
74
Replies
3
Views
73
Replies
10
Views
154