How to count unique days in 2 different columns that are within an specific month?

[reivillar]

How can I create a formula that counts the unique days in 2 different columns within a specific month? (result in cells F3 and F4)

In the example below: 4 unique days for Oct/2020 and 5 unique days in Nov/2020.

Thanks in advance!

 

[bebo021999]

Is month in column E: Real date (formatted as "mmm-yy") or text string?
Any problem if, it to be real 1st day of month: "1-Oct-2020"?

 

[bebo021999]

With my suggest in #2
Reference instruction guide how to build it in: Count unique distinct values in two columns

Book1
	A	B	C	D	E	F
1
2	23-Oct-21	xx	28-Oct-21		Month	Count
3	26-Oct-21	y	04-Nov-21		01-Oct-21	4
4	26-Oct-21	xx	04-Nov-21		01-Nov-21	5
5	26-Oct-21	y	04-Nov-21
6	26-Oct-21	t	04-Nov-21
7	22-Oct-21	r	04-Nov-21
8	22-Oct-21	g	04-Nov-21
9	22-Oct-21	h	05-Nov-21
10	05-Nov-21	j	06-Nov-21
11	05-Nov-21	k	06-Nov-21
12	05-Nov-21	l	06-Nov-21
13	05-Nov-21	;	09-Nov-21
14	26-Oct-21	i	10-Nov-21
Sheet2

Cell Formulas
Range	Formula
F3:F4	F3	=SUM(IF($A$2:$A$14>=$E3,IF($A$2:$A$14<EDATE($E3,1),1/COUNTIFS($A$2:$A$14,">="&$E3,$A$2:$A$14,"<"&EDATE($E3,1),$A$2:$A$14,$A$2:$A$14))))+SUM(IF(COUNTIF($A$2:$A$14,$C$2:$C$14)=0,IF($C$2:$C$14>=$E3,IF($C$2:$C$14<EDATE($E3,1),1/COUNTIFS($C$2:$C$14,">="&$E3,$C$2:$C$14,"<"&EDATE($E3,1),$C$2:$C$14,$C$2:$C$14)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

If you have Excel 365, the shorter G3 formula should work:

Book1
	A	B	C	D	E	F	G	H
1
2	10/23/2021	xx	10/28/2021		Month	Count
3	10/26/2021	y	11/4/2021		10/1/2021	4	4	4
4	10/26/2021	xx	11/4/2021		11/1/2021	5	5	5
5	10/26/2021	y	11/4/2021
6	10/26/2021	t	11/4/2021
7	10/22/2021	r	11/4/2021
8	10/22/2021	g	11/4/2021
9	10/22/2021	h	11/5/2021
10	11/5/2021	j	11/6/2021
11	11/5/2021	k	11/6/2021
12	11/5/2021	l	11/6/2021
13	11/5/2021	;	11/9/2021
14	10/26/2021	i	11/10/2021
15
Sheet10

Cell Formulas
Range	Formula
F3:F4	F3	=SUM(IF($A$2:$A$14>=$E3,IF($A$2:$A$14<EDATE($E3,1),1/COUNTIFS($A$2:$A$14,">="&$E3,$A$2:$A$14,"<"&EDATE($E3,1),$A$2:$A$14,$A$2:$A$14))))+SUM(IF(COUNTIF($A$2:$A$14,$C$2:$C$14)=0,IF($C$2:$C$14>=$E3,IF($C$2:$C$14<EDATE($E3,1),1/COUNTIFS($C$2:$C$14,">="&$E3,$C$2:$C$14,"<"&EDATE($E3,1),$C$2:$C$14,$C$2:$C$14)))))
G3:G4	G3	=SUM(IF(COUNTIF($A$2:$A$14,SEQUENCE(EOMONTH(E3,0)-E3+1,,E3))+COUNTIF($C$2:$C$14,SEQUENCE(EOMONTH(E3,0)-E3+1,,E3)),1))
H3:H4	H3	=SUM(IF(MMULT(COUNTIF(OFFSET($A$2:$A$14,0,{0,2}),SEQUENCE(EOMONTH(E3,0)-E3+1,,E3)),{1;1}),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

If the columns to check are the same size but just separated by some number of columns, you can get even shorter with the H3 formula.

These formulas are backward compatible with earlier versions of Excel by replacing the SEQUENCE function:

Book1
	A	B	C	D	E	F	G	H
1
2	10/23/2021	xx	10/28/2021		Month	Count
3	10/26/2021	y	11/4/2021		10/1/2021	4	4	4
4	10/26/2021	xx	11/4/2021		11/1/2021	5	5	5
5	10/26/2021	y	11/4/2021
6	10/26/2021	t	11/4/2021
7	10/22/2021	r	11/4/2021
8	10/22/2021	g	11/4/2021
9	10/22/2021	h	11/5/2021
10	11/5/2021	j	11/6/2021
11	11/5/2021	k	11/6/2021
12	11/5/2021	l	11/6/2021
13	11/5/2021	;	11/9/2021
14	10/26/2021	i	11/10/2021
15
Sheet2

Cell Formulas
Range	Formula
F3:F4	F3	=SUM(IF($A$2:$A$14>=$E3,IF($A$2:$A$14<EDATE($E3,1),1/COUNTIFS($A$2:$A$14,">="&$E3,$A$2:$A$14,"<"&EDATE($E3,1),$A$2:$A$14,$A$2:$A$14))))+SUM(IF(COUNTIF($A$2:$A$14,$C$2:$C$14)=0,IF($C$2:$C$14>=$E3,IF($C$2:$C$14<EDATE($E3,1),1/COUNTIFS($C$2:$C$14,">="&$E3,$C$2:$C$14,"<"&EDATE($E3,1),$C$2:$C$14,$C$2:$C$14)))))
G3:G4	G3	=SUM(IF(COUNTIF($A$2:$A$14,ROW(INDIRECT(E3&":"&EOMONTH(E3,0))))+COUNTIF($C$2:$C$14,ROW(INDIRECT(E3&":"&EOMONTH(E3,0)))),1))
H3:H4	H3	=SUM(IF(MMULT(COUNTIF(OFFSET($A$2:$A$14,0,{0,2}),ROW(INDIRECT(E3&":"&EOMONTH(E3,0)))),{1;1}),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[reivillar]

You are amazing. The shorter version (H3) worked perfectly!

 

[Eric W]

Glad we could help!