Collecting data from a specific date range.

[HAYLO22]

I have a worksheet with lots of information on it. I have created a summary sheet which gathers information on for example:

How many clients we have
How many clients each instructor has
How many clients have completed their referral
How many clients where removed from scheme
How many clients are pending
All of this data has been collected, using formulas such as this one:

=COUNTIFS(ExerciseReferral!E3:E674,"Annette",ExerciseReferral!F3:F674,"Completed")
This is an example of how many Completed clients Annette has.

The total completed is the overall on the whole spreadsheet which is a few years - but I would like to find out the total completed from the new financial year (April 1st 2022).

Can anyone help with me the correct formula?
It would be good if I could just add to this one, as I am a bit of a newbie with Excel.

Thank you

 

[Joe4]

Welcome to the Board!

You can add more criteria in your COUNTIFS to include date ranges.
See: Excel Date Range Sum Amounts or Count Items Examples

 

[HAYLO22]

Welcome to the Board!

You can add more criteria in your COUNTIFS to include date ranges.
See: Excel Date Range Sum Amounts or Count Items Examples

Thank you - I have had a look and tried to follow the instructions, but I cannot for the life of me get it to work. I don't know what I am doing wrong.
Using the example of the COUNTIFS formula I gave, are you able to add the start date to be the 1st of April 2022 please? Just so I have an idea of how to do it, so that I can do it to the rest please?

I have put the start date as 01/04/2022 in L2 and the end date as a formula of "=TODAY()" in L3

Hopefully this all makes sense?

Thanks for your time

 

[Joe4]

Since you have not posted any data example, we have no idea what the structure of your data is.
Can you post a small sample of your data?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Or, can you at least tell us what column these dates are in in your data?

 

[HAYLO22]

=COUNTIFS(ExerciseReferral!E3:E674,"Annette",ExerciseReferral!F3:F674,"Completed")

I have put the start date as 01/04/2022 in L2 and the end date as a formula of "=TODAY()" in L3
The dates are in ExerciseReferral!D3:D400
Does that help? It's difficult to show the info without show too much data. I'll try see if there is anymore info I can give if that isn't enough.

Thank you

 

[Joe4]

Try:
=COUNTIFS(ExerciseReferral!E3:E674,"Annette",ExerciseReferral!F3:F674,"Completed",ExerciseReferral!D3:D674,">=" & $L$2,ExerciseReferral!D3:D674,"<=" & $L$3)

 

[HAYLO22]

That worked! Thank you so much for taking the time to help me.

 

[Joe4]

You are welcome.
Hopefully, you can see what I did (I just took the info from the link I gave you and incorporated it into your original formula).

 

[HAYLO22]

Thanks! Yeah you did exactly what I was trying to do but for some reason it wasn’t working. I may have put a comma or space or something in the wrong place or something along those lines!

Thanks so much!

 

[Joe4]

You are welcome.

Note then marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this for you on this thread.