Seeking help with excel formula to count unique text within a date range

[jmavillarroel]

I am looking to count the total (unduplicated) number of children that were seen in the month of April (between 4/1/2020 and 4/30/2020) using an excel formula. Sumproduct? Countifs? (some cells may have blanks) Thank you for any help!!

Date Seen	Youth Name
3/31/2020	Angel Rodriguez
3/31/2020	Tim Smith
4/1/2020
4/2/2020	Jakob Young
4/3/2020	Chloe Flowers
4/6/2020	Mriah Boswell
4/6/2020	Travonna Paine
4/7/2020	Aidan Boyles
4/7/2020	Chloe Flowers
4/7/2020	Cheyna Rain
4/7/2020	Moses Josephs
4/8/2020	Jason Boswell
4/8/2020	Grace Willows
4/9/2020	Matthew Cox
4/9/2020	Christian Jackson
4/10/2020	Starasia Lebel
4/10/2020	Zachary Timmons
4/13/2020	Breanna Stewart
4/13/2020	Dominic Lane
4/13/2020	Zachary Timmons
4/14/2020	Moses Josephs
4/15/2020	Kailey Stewart
4/16/2020	Chloe Flowers
4/16/2020	Matthew Cox
4/16/2020	Christian Jackson
4/16/2020	Brooke Shields
4/17/2020	Starasia Lebel
4/17/2020	Chayton Drake
4/20/2020	Mriah Boswell
4/20/2020	Travonna Paine
4/20/2020	Christopher Klein
4/20/2020	Zachary Timmons
4/21/2020	Dominic Lane
4/21/2020	Moses Josephs
4/21/2020	Destiny Boysen
4/21/2020	Cheyna Rain
4/22/2020	Jason Boswell
4/23/2020	Christian Jackson
4/24/2020	Chayton Drake
4/24/2020	Christian Braden
4/24/2020	Brooke Shields
4/28/2020	Robert Lee
4/28/2020	Dominic Lane
4/29/2020	Grace Willows
4/29/2020	Kailey Stewart
4/29/2020	Zachary Timmons
5/1/2020	Joe Smith

 

[alansidman]

One way of doing this is with Power Query. Filter out March and May. Determine Unique items. Count the number of Youths.

Here is the Mcode for that procedure.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Seen", type date}, {"Youth Name", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date Seen] <> #date(2020, 3, 31) and [Date Seen] <> #date(2020, 5, 1))),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Youth Name"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Youth Name] <> null)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 1, 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Count"})
in
#"Removed Columns"

 

[AhoyNC]

Try this array formula. Change ranges to match your data.
Formula must be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D
1	Date Seen	Youth Name
2	3/31/2020	Angel Rodriguez	Start	4/1/2020
3	3/31/2020	Tim Smith	End	4/30/2020
4	4/1/2020
5	4/2/2020	Jakob Young	Count	22
6	4/3/2020	Chloe Flowers
7	4/6/2020	Mriah Boswell
8	4/6/2020	Travonna Paine
9	4/7/2020	Aidan Boyles
10	4/7/2020	Chloe Flowers
11	4/7/2020	Cheyna Rain
12	4/7/2020	Moses Josephs
13	4/8/2020	Jason Boswell
14	4/8/2020	Grace Willows
15	4/9/2020	Matthew Cox
16	4/9/2020	Christian Jackson
17	4/10/2020	Starasia Lebel
18	4/10/2020	Zachary Timmons
19	4/13/2020	Breanna Stewart
20	4/13/2020	Dominic Lane
21	4/13/2020	Zachary Timmons
22	4/14/2020	Moses Josephs
23	4/15/2020	Kailey Stewart
24	4/16/2020	Chloe Flowers
25	4/16/2020	Matthew Cox
26	4/16/2020	Christian Jackson
27	4/16/2020	Brooke Shields
28	4/17/2020	Starasia Lebel
29	4/17/2020	Chayton Drake
30	4/20/2020	Mriah Boswell
31	4/20/2020	Travonna Paine
32	4/20/2020	Christopher Klein
33	4/20/2020	Zachary Timmons
34	4/21/2020	Dominic Lane
35	4/21/2020	Moses Josephs
36	4/21/2020	Destiny Boysen
37	4/21/2020	Cheyna Rain
38	4/22/2020	Jason Boswell
39	4/23/2020	Christian Jackson
40	4/24/2020	Chayton Drake
41	4/24/2020	Christian Braden
42	4/24/2020	Brooke Shields
43	4/28/2020	Robert Lee
44	4/28/2020	Dominic Lane
45	4/29/2020	Grace Willows
46	4/29/2020	Kailey Stewart
47	4/29/2020	Zachary Timmons
48	5/1/2020	Joe Smith
Sheet2

Cell Formulas
Range	Formula
D5	D5	=SUM(IF(FREQUENCY(IF($B$2:$B$48<>"",IF($A$2:$A$48>=$D$2,IF($A$2:$A$48<=$D$3,MATCH($B$2:$B$48,$B$2:$B$48,0)))),ROW($B$2:$B$48)-ROW($B$2)+1),1))