Couple of Questions

[Irwell]

Hi Folks,

Not used excel in a while and my brain is hazy from the weekend so hopefully the good folks of MRexcel will be able to steer me away from potential wasted time by helping me out with a few simple questions.

I have 2 tabs on a spreadsheet one with data in and one the summary sheet.

In the data sheet in column E is a list of dates where clients have started.
In the data sheet in column AR is a list of dates where clients have discharged.

In the summary I want it to count how many were on caseload each month.

The calculation I was thinking of was the following
=COUNTIF('Tier 3 Tracker'!$E:$E,"<="&"30/04/2017")-COUNTIF('Tier 3 Tracker'!$AR:$AR,"<="&"31/03/2017")

Then change the dates for each following month etc, only issue I have is a few of the months don't seem to add up and I just can't fathom why, am I missing something?


EDIT: On the back of that, I also need to pull a figure from one column that counts the number of people with a certain need as a snapshot at the end of each financial quarter, how would you go about doing this, any suggestions?

 

[Irwell]

Returning one less than it should

=COUNTIF('Tier 3 Tracker'!$E:$E,"<=30/09/2017")-COUNTIF('Tier 3 Tracker'!$AR:$AR,"<=30/06/2017")

Is there something wrong with this formula, there is 18 dates that are eligible in column E after taking out the dates in column AR but it's only returning 17 on the formula?

Date started	Discharge Date
12/04/2017
20/07/2017
17/05/2017
02/08/2017
28/07/2017
25/10/2016
18/04/2017
30/05/2017
29/06/2017
08/07/2016
27/07/2016
19/04/2016
27/01/2017
17/03/2017
01/11/2017
21/03/2017	02/05/2017
28/02/2017	22/05/2017
17/03/2017	15/05/2017
14/06/2016	22/05/2017
09/11/2015	24/05/2017
06/04/2017	05/06/2017
	27/06/2017
08/02/2017	10/07/2017
12/04/2017	17/08/2017
12/01/2016	25/07/2017
30/03/2017	26/07/2017

<colgroup><col><col></colgroup><tbody>
</tbody>

The column on the left is E and the column on the right is AR

 

[Jonmo1]

Re: Returning one less than it should

Separate the 2 countifs, and see which one is returning the wrong value

What do they return independently ?
=COUNTIF('Tier 3 Tracker'!$E:$E,"<=30/09/2017")
=COUNTIF('Tier 3 Tracker'!$AR:$AR,"<=30/06/2017")

If I had to guess, you're trying to count between 30/06 and 30/09

You probably want to change the <= to < on the 2nd countifs

 

[Joe4]

Re: Returning one less than it should

I merged your two threads together. Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

In regards to your question, it would be helpful if you could post the data you have in columns E and AR. Then we can recreate your exact situation.

 

[Irwell]

Re: Returning one less than it should

****! I've just figured it out. It's because one date has something in on the discharge and no start date. Sorry people.