(U - - - - t ) if multiple conditions then return value

[kkdarkyyy]

Hi all,

I tried to think of a formula to get the value for column C and D but not success. Please Help.

Column C criteria :
Return 1 when there is exactly 1 record for column A(e.g Mary) in the past 11 months(i.e. excluding current month record) in column B

e.g. Cell C3 return 1 because there is 1 record (2023/1/1) for Mary in the past 11 months (i.e. period from 2022/03/01-2023/01/31)
e.g. Cel C5 return null because there is no record for Mary (column A) in the past 11 months (i.e. from 2025/05/01-2026/03/31)

Column D criteria :
Return 1 when there are exactly 2 records for column A(e.g Mary)in the past 11 months(i.e. excluding current month record) in column B

e.g. Cell D4 return 1 because there are 2 records (2023/1/1, 2023/2/1) for Mary in last 11 months (i.e. period from 2022/04/01-2023/02/28)

Assumption for Column B:
1: There is only 1 record for each person in each month
2: always the 1 day of each month

Name	YYYYMMDD	Alert count (Return 1 when there is 1 record in the past 11 months(i.e. excluding current month record))	Alert count (Return 1 when there are 2 records in the past 11 months(i.e. excluding current month record))
Mary	2023/1/1
Mary	2023/2/1	1
Mary	2023/3/1	1	1
Mary	2026/4/1
Mary	2026/5/1	1
Mary	2026/6/1	1	1
Peter	2021/1/1
Peter	2021/2/1	1
Peter	2022/4/1
Peter	2022/5/1	1
Peter	2025/6/1
Peter	2025/7/1	1
Peter	2025/8/1	1	1

 

[bosco_yip]

Try,

[C2] =IFERROR(IF((DATEDIF(B1,B2,"M")>=1)*(DATEDIF(B1,B2,"M")<=11),1,""),"")

[D2] =IF(DATEDIF(MIN(B2,B3),MAX(B2,B3),"M")>=11,1,"")

All copied down.