Count cells that are contain multiple criteria (dates)

maxim642

Board Regular
Joined
Feb 4, 2021
Messages
91
Office Version
  1. 365
Platform
  1. MacOS
I would like to count the number of contracts being worked on at specific periods in time (based on the date input into G1, with these numbers split into Fixed and Perm, cells G2 & G3 respectively.

To count the number of contracts, the calculation should be summing the number of records that are EQUAL TO OR BELOW the date in G1 AND the contract finish date is EQUAL TO OR GREATER than the date in G1 OR the contract finish date is blank (so the contract is still ongoing, so they should be counted).

HR Dashboard - Anonymous v1.xlsx
ABCDEFG
1IDContract StartContract FinishTypeEnter Date
24810/20/1912/19/19FixedTotal Fixed
3104/25/21FixedTotal Perm
4881/24/183/25/18Fixed
5487/2/218/31/21Fixed
66311/6/161/5/17Fixed
7712/16/19Fixed
89710/8/1912/7/19Fixed
9291/31/163/31/16Fixed
10818/18/18Fixed
119512/30/202/28/21Fixed
12761/22/173/23/17Fixed
13135/21/167/20/16Fixed
14522/9/20Fixed
1581/28/183/29/18Perm
16125/15/21Perm
17204/6/166/5/16Perm
18439/23/1811/22/18Perm
19715/4/187/3/18Perm
20751/3/163/3/16Perm
21895/19/20Perm
22836/7/188/6/18Perm
23492/16/21Perm
24275/9/197/8/19Perm
25344/29/176/28/17Perm
Sheet3
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
How about
+Fluff 1.xlsm
ABCDEFG
1IDContract StartContract FinishTypeEnter Date01/01/2021
24820/10/201919/12/2019FixedTotalFixed4
31025/04/2021FixedTotalPerm1
48824/01/201825/03/2018Fixed
54802/07/202131/08/2021Fixed
66306/11/201605/01/2017Fixed
77116/02/2019Fixed
89708/10/201907/12/2019Fixed
92931/01/201631/03/2016Fixed
108118/08/2018Fixed
119530/12/202028/02/2021Fixed
127622/01/201723/03/2017Fixed
131321/05/201620/07/2016Fixed
145209/02/2020Fixed
15828/01/201829/03/2018Perm
161215/05/2021Perm
172006/04/201605/06/2016Perm
184323/09/201822/11/2018Perm
197104/05/201803/07/2018Perm
207503/01/201603/03/2016Perm
218919/05/2020Perm
228307/06/201806/08/2018Perm
234916/02/2021Perm
242709/05/201908/07/2019Perm
253429/04/201728/06/2017Perm
26
Lists
Cell Formulas
RangeFormula
G2:G3G2=SUMPRODUCT(($D$2:$D$25=F2)*($B$2:$B$25<=$G$1)*(($C$2:$C$25>=$G$1)+($C$2:$C$25="")))
 
Upvote 0
Solution
How about
+Fluff 1.xlsm
ABCDEFG
1IDContract StartContract FinishTypeEnter Date01/01/2021
24820/10/201919/12/2019FixedTotalFixed4
31025/04/2021FixedTotalPerm1
48824/01/201825/03/2018Fixed
54802/07/202131/08/2021Fixed
66306/11/201605/01/2017Fixed
77116/02/2019Fixed
89708/10/201907/12/2019Fixed
92931/01/201631/03/2016Fixed
108118/08/2018Fixed
119530/12/202028/02/2021Fixed
127622/01/201723/03/2017Fixed
131321/05/201620/07/2016Fixed
145209/02/2020Fixed
15828/01/201829/03/2018Perm
161215/05/2021Perm
172006/04/201605/06/2016Perm
184323/09/201822/11/2018Perm
197104/05/201803/07/2018Perm
207503/01/201603/03/2016Perm
218919/05/2020Perm
228307/06/201806/08/2018Perm
234916/02/2021Perm
242709/05/201908/07/2019Perm
253429/04/201728/06/2017Perm
26
Lists
Cell Formulas
RangeFormula
G2:G3G2=SUMPRODUCT(($D$2:$D$25=F2)*($B$2:$B$25<=$G$1)*(($C$2:$C$25>=$G$1)+($C$2:$C$25="")))
Thank you once again!!!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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