shorten the formula

Shazir

Banned - Rules violations
Joined
Jul 28, 2020
Messages
94
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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})
 
Upvote 0
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})
 
Upvote 0
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})
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top