Countifs values between 2 dates.

[Tdoulis]

This one is doing my head in. I’m trying to build a labour availability schedule which extracts data from a gannt chart and a leave roster and need check availability of a tradesman. To do this I thought the best way would be to do a countifs function with 3 criteria 1) Tradesman Name 2) Start date 3) End Date.
I’m using the formula =COUNTIFS(GANNTDATA[TRADESMAN],$B14,GANNTDATA[START],">="&F$13,GANNTDATA[END],"<="&G$13) but the only value that is working is where there just one tradesman and the start and end date are the same.

Can someone give me a heads up as to what I’m doing wrong.

[AliGW]

Welcome to the forum.

Please add some manually calculated expected results to your workbook. A non-working formula tells us nothing,

[Tdoulis]

AliGW,
Please excuse my ignorance as I am new to this forum… what exactly do you mean by “manually calculated expected results”?

As you’ll see n the worksheet provided the Tradesman Andrew Crossman has a task starting on the 5/10/21 and ends 5/1/2 and again starting on the 3/8/2 to the 10/1/2 but this is not getting picked up on the availability worksheet.

Instantly if I just had the start date it’ll note that there is 6 occurrences starting at the earliest date but take it right through to the end of the year as there is no end date.

Again I apologise if I’m wording the question correctly or providing the relevant information.

[AliGW]

1. A sample dataset should be a SMALL sub-set of your real data (desensitised). There should be just enough data to show the problem.
2. In the sample workbook, you should show EXPECTED RESULTS, manually entered, not formulae that are not doing what you want. So instead of a screen full of incorrect results, I want to see a few rows of results that you want to see. You will need to calculate these manually in your (small) sample dataset.

[Tdoulis]

I’ve added a updated copy of the workbook with a worksheet showing the expected results. I hope this helps.

[alidurfani]

First thing is that your dates in Availability tab looks like dd/mm/yyyy
and
in Data tab it's mm/dd/yyyy
you need to update both tabs to mm/dd/yyyy

You need to also include End Date in Availability tab and include that in COUNTIFS criteria 3

I don't have time to update all but you can see attached spreadsheet column CL, I have updated it I have added a row (No. 13) for End date

Formula is:
=COUNTIFS(GANNTDATA[TRADESMAN],$B15,GANNTDATA[START],">="&CL$14,GANNTDATA[END],"<="&CL$13)

[Tdoulis]

Thanks alidurfani, however I don't understand the logic in your response.

In the workbook I have uploaded, both dates are formatted the same... dd/mm/yyyy. nonetheless I take your point that the dates are to be formatted the same.

However, where is the relevance to the end date you have inserted in cell CL13 on the availability tab? I assume you just looked up on the data tab and inserted the end date associated with the tradesman's name on B16 "Andrew Crossman" This is impractical as each tradesmen will have a varying end date. I cant just lookup the Data Sheet and get an end date, it sort of defeats the purpose of we are trying to achieve.

I have tried to use your logic and have a different start and end date lookup value by using the next date. i.e. if the Start Date I'm looking up in CL14 is 30/08/2021 then my lookup End Date will be 31/08/21. therefore, what I am asking is to count the days Andrew Crossman is working on the days starting 30/8/21 to the 31/8/21 and the result should be 1

=COUNTIFS(GANNTDATA[TRADESMAN],$B16,GANNTDATA[START],">="&CL$14,GANNTDATA[END],"<="&CM$14)

Nonetheless I appreciate your response but on this occasion I don't believe Solved.

[JeteMc]

Perhaps these two changes will help.
1. The values in F13:II13 on the Availability sheet were text, so I changed them to dates (I also left the data on that sheet as a range instead of a table).
2. Changed the formula to read: =COUNTIFS(GANNTDATA[TRADESMAN],$B14,GANNTDATA[START],"<="&F$13,GANNTDATA[END],">="&F$13)
Let us know if you have any questions.