formula to check if month and exists in Col F

[howard]

I have dates in Col F onn sheet "Data"


i would like a formula to check whether a month and year exits that is linked to Cell M1 (M1 contains the month and Year) that I need to search if it exits in ColF on sheet "Data"

Your assistance is most appreciated

 

[hrayani]

Hi,

Try this to check months

=SUMPRODUCT(1*(MONTH(Data!F2:F7)=MONTH(M1)))

 

[hrayani]

This is to check for both month & year

=IF(AND(SUMPRODUCT(1*(MONTH(Data!F2:F7)=MONTH(M1))),(SUMPRODUCT(1*(YEAR(Data!F2:F7)=YEAR(M1))))),TRUE,FALSE)

 

[howard]

Thanks for the help. Kindly explain how the sumproduct works in this example

 

[Toadstool]

It depends how M1 date is held. Assuming it is an actual date then:

Howard2.xlsx
	F	G					L	M
1	Dates						Month/Year	Jan-21
2	01-Jan-20						Exists?	Yes
3	18-Mar-20						How many?	2
4	03-Jun-20
5	19-Aug-20
6	04-Nov-20
7	20-Jan-21
8	07-Apr-21
9	23-Jun-21
10	08-Sep-21
11	24-Nov-21
12	30-Jan-21
13	27-Apr-22
Sheet1

Cell Formulas
Range	Formula
M2	M2	=IF(COUNTIFS(F:F,">="&DATE(YEAR(M1),MONTH(M1),1),F:F,"<="&EOMONTH(M1,0))>0,"Yes","No")
M3	M3	=COUNTIFS(F:F,">="&DATE(YEAR(M1),MONTH(M1),1),F:F,"<="&EOMONTH(M1,0))

 

[howard]

Thanks for the help Toadstool