shorten the formula

Shazir

Board Regular
Joined
Jul 28, 2020
Messages
63
Office Version
  1. 365
Platform
  1. 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
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,336
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
Joined
Jul 28, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
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
Joined
Jan 15, 2007
Messages
23,804
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,485
Members
410,685
Latest member
chandraganji
Top