Jun 22 2022 07:53 PM
Hi ,
I need to look at the course start and end date. Then in a given month I want to find if the course was running. For a given month I want to find how many courses run.
I tries countifs , which was not working well for me.
Data set:
Group No | Start date | End date |
1 | 18/01/2021 | 12/11/2021 |
2 | 1/02/2021 | 8/10/2021 |
3 | 22/02/2021 | 23/12/2021 |
4 | 22/03/2021 | 29/01/2022 |
5 | 3/05/2021 | 25/12/2021 |
6 | 26/04/2021 | 5/03/2022 |
7 | 5/07/2021 | 29/04/2022 |
8 | 12/07/2021 | 20/05/2022 |
9 | 9/08/2021 | 17/06/2022 |
10 | 16/08/2021 | 22/04/2022 |
11 | 13/09/2021 | 15/07/2022 |
12 | 5/10/2021 | 6/08/2022 |
13 | 25/10/2021 | 1/07/2022 |
14 | 13/12/2021 | 14/10/2022 |
15 | 17/01/2022 | 23/09/2022 |
16 | 14/02/2022 | 16/12/2022 |
17 | 21/02/2022 | 23/12/2022 |
18 | 28/02/2022 | 4/11/2022 |
19 | 21/03/2022 | 20/01/2023 |
20 | 11/04/2022 | 10/02/2023 |
21 | 2/05/2022 | 6/01/2023 |
22 | 16/05/2022 | 17/03/2023 |
23 | 30/05/2022 | 31/03/2023 |
24 | 18/07/2022 | 19/05/2023 |
25 | 15/08/2022 | 16/06/2023 |
26 | 12/09/2022 | 14/07/2023 |
27 | 17/10/2022 | 23/06/2023 |
Output table required
I made up the number.
Month | No of classes running |
January - 2021 | 2 |
Feb - 2021 | 5 |
........ | .... |
January 2022 | 7 |
Jun 23 2022 02:13 AM
Hi @Imi_Jay
With the data you sharedI can't reconcile your "manual" counts/totals
Below are courses that have a Start date in 2021 only:
- Columns F-Q are Jan 2021-Dec 2021
- Row 17 is my count of running courses
Where's my mistake/misunderstanding?
Jun 23 2022 02:52 AM - edited Jun 23 2022 09:43 AM
Hi @Imi_Jay
Please check whether the following solution is helpful:
Formula in cell F2 is: =COUNTIFS($B$2:$B$28,"<="&EOMONTH(E2,0),$C$2:$C$28,">="&E2)
NOTE
1. Values in range E2:E14 are dates: 1-Jan-2021, 1-Feb-2021,1-Mar-2021 . . . 1-1-2022.
I applied Custom Formatting to make these dates appear as mmm-yyyy.
2. In the above suggested formula, please change cell reference/ranges to suit Your requirement.
3. Please drag the formula down to more rows.
Please respond if You require further assistance. I will try My best to be of help.
If I was able to help You, please Like My answer and mark it as the Best Response.
Thank You!