unique/count/filter dates into days, months, and years.

[Ev1lZer0]

I feel like I was getting close to a solution, but just not quite getting there...

I am trying to count all the unique days, months, and years in column C and then get a total for each time they are used.

Here is my current spreadsheet, I have manually typed in my data where the formula should go so that you see what I'm trying to capture. this list will be an ever-growing list.

I also have a goal of creating a bar graph for each data point. One graph totaling all entries for each date, one graph totaling all entries for each month (with the appropriate year), and one graph totaling all entries for each year.

I have previously had luck with this formula, but this data is not counting and filtering text, it's filtering dates. =UNIQUE(FILTER(F2:F22525,LEFT(F2:F22525,6)="judge:"))

Inmates in Custody with Military Service (SERV) Flag Set.xlsx
	A	B	C	D	E	F	G	H
1	Yakima County Department of Corrections
2	Inmates in Custody with Military Service Flag Set
3
4	1/10/2023
5
6	Name	Booking Date	Unique Days	Count	Unique Months	Count	Unique Years	Count
7	Gomez, Victor	1/23/2020	1/23/2020	1	Jan-20	1	2020	1
8	Mcginnis, Nathan Lee	4/16/2021	4/16/2021	1	Apr-21	1	2021	2
9	Pedroza-Lewis, Jonathan Juan	9/1/2021	9/1/2021	1	Sep-23	1	2022	5
10	Wertman, James Patrick	4/25/2022	4/25/2022	1	Apr-23	1	2023	1
11	Robinson, Neal Scott	6/18/2022	6/18/2022	1	Jun-22	1
12	Corbray, Jacquita Louise	8/23/2022	8/23/2022	1	Aug-22	1
13	Balzer, Daniel Eugene	11/10/2022	11/10/2022	1	Nov-22	2
14	Brown, Maurice Terrell	11/11/2022	11/11/2022	1	Jan-23	1
15	Tenney, Nickalas John	1/7/2023	1/7/2023	2
16	Glossip, Larry Bruce	1/7/2023
17	Total			10		10		10
Sheet1

Cell Formulas
Range	Formula
C7:C15	C7	=UNIQUE(B7:B16)
Dynamic array formulas.

 

[Ev1lZer0]

Okay, I've made some changes and found solutions for the day and year, but I can't find a solution for the month.

I know it doesn't seem it, but column E is displaying accurate information, but as soon as I format it into a date or use the Text function, it becomes incorrect. That is because the number in cell E2 (12020) is equivalent to the date 11/27/1932. however, 12020 with a space would read 1 2020 which is January 2020. Needless to say, I'm at a loss for how to make this work for finding the unique month with the accompanying year and getting a total entry count.

Master list.xlsx
	A	B	C	D	E	F	G	H
1	Booking Date		Unique Dates	Total entries of this date	Unique Month/Years	Total entries of this month/year	Years
2	01/23/20		01/23/20	6	12020	0	2020	6
3	01/23/20		04/16/21	6	42021	0	2021	12
4	01/23/20		09/01/21	6	92021	0	2022	32
5	01/23/20		04/25/22	6	42022	0	2023	9
6	01/23/20		06/18/22	6	62022	0
7	01/23/20		08/23/22	6	82022	0
8	04/16/21		11/10/22	6	112022	0
9	04/16/21		11/17/22	2	122022	0
10	04/16/21		12/05/22	6	12023	0
11	04/16/21		01/07/23	9
12	04/16/21
13	04/16/21
14	09/01/21
15	09/01/21
16	09/01/21
17	09/01/21
18	09/01/21
19	09/01/21
20	04/25/22
21	04/25/22
22	04/25/22
23	04/25/22
24	04/25/22
25	04/25/22
26	06/18/22
27	06/18/22
28	06/18/22
29	06/18/22
30	06/18/22
31	06/18/22
32	08/23/22
33	08/23/22
34	08/23/22
35	08/23/22
36	08/23/22
37	08/23/22
38	11/10/22
39	11/10/22
40	11/10/22
41	11/10/22
42	11/10/22
43	11/10/22
44	11/17/22
45	11/17/22
46	12/05/22
47	12/05/22
48	12/05/22
49	12/05/22
50	12/05/22
51	12/05/22
52	01/07/23
53	01/07/23
54	01/07/23
55	01/07/23
56	01/07/23
57	01/07/23
58	01/07/23
59	01/07/23
60	01/07/23
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=UNIQUE(A2:A60)
E2:E10	E2	=UNIQUE((MONTH(C2#))&(YEAR(C2#)))
G2:G5	G2	=UNIQUE(YEAR(C2#))
H2:H5	H2	=SUMPRODUCT((YEAR($A$2:$A$60)=$G2)+0)
F2	F2	=SUMPRODUCT((MONTH($A$2:$A$60)=$E2)+0)
F3:F10	F3	=COUNTIF(A:A,E3)
D2:D11	D2	=COUNTIF(A:A,C2)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$A$1	F3:F10, D2:D11

 

[Coyotex3]

Just curious, have you tried using a Pivot Table instead of Formulas? Maybe a pivot table can handle this?

Book22
	A	B
1
2
3	Row Labels	Count of Booking Date
4	2020	6
5	2021	12
6	2022	32
7	2023	9
8	Grand Total	59
9
10
Sheet2

Book22
	A	B
2
3	Row Labels	Count of Booking Date (Year)
4	1/23/2020	6
5	4/16/2021	6
6	9/1/2021	6
7	4/25/2022	6
8	6/18/2022	6
9	8/23/2022	6
10	11/10/2022	6
11	11/17/2022	2
12	12/5/2022	6
13	1/7/2023	9
14	Grand Total	59
Sheet3

Book22
	A	B
3	Row Labels	Count of Booking Date (Month)
4	Jan	15
5	Apr	12
6	Jun	6
7	Aug	6
8	Sep	6
9	Nov	8
10	Dec	6
11	Grand Total	59
Sheet4

 

[jasonb75]

Is this what you need?

Note that the dates in column E use a custom format of mm/yyyy

Book2
	A	B	C	D	E	F	G	H
6	Name	Booking Date	Unique Days	Count	Unique Months	Count	Unique Years	Count
7	Gomez, Victor	01/23/2020	01/23/2020	1	01/2020	1	2020	1
8	Mcginnis, Nathan Lee	04/16/2021	04/16/2021	1	04/2021	1	2021	2
9	Pedroza-Lewis, Jonathan Juan	09/01/2021	09/01/2021	1	09/2021	1	2022	5
10	Wertman, James Patrick	04/25/2022	04/25/2022	1	04/2022	1	2023	2
11	Robinson, Neal Scott	06/18/2022	06/18/2022	1	06/2022	1
12	Corbray, Jacquita Louise	08/23/2022	08/23/2022	1	08/2022	1
13	Balzer, Daniel Eugene	11/10/2022	11/10/2022	1	11/2022	2
14	Brown, Maurice Terrell	11/11/2022	11/11/2022	1	01/2023	2
15	Tenney, Nickalas John	01/07/2023	01/07/2023	2
16	Glossip, Larry Bruce	01/07/2023
17	Total			10		10		10
Sheet1

Cell Formulas
Range	Formula
C7:C15	C7	=UNIQUE(B7:B16)
E7:E14	E7	=UNIQUE(DATEVALUE(TEXT(C7#,"mm/yyyy")))
G7:G10	G7	=UNIQUE(YEAR($B$7:$B$16))
H7:H10	H7	=COUNTIFS($B$7:$B$16,">="&DATE(G7,1,1),$B$7:$B$16,"<="&DATE(G7,12,31))
F7:F14	F7	=COUNTIFS($B$7:$B$16,">="&E7,$B$7:$B$16,"<="&EOMONTH(E7,0))
D17,H17,F17	D17	=SUM(D7:D16)
Dynamic array formulas.

 

[Ev1lZer0]

The results you have are exactly how I need it.

Can you walk me through how to create this custom format?

 

[Ev1lZer0]

Nevermind, I had the format right, but I incorrectly copied the formula.

Thank you for your help!