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

#### kkdarkyyy

##### New Member
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

(Return 1 when there is 1 record in the past 11 months(i.e. excluding current month record))
(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
11
Mary
 2026/4/1
Mary
 2026/5/1
1
Mary
 2026/6/1
11
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
11

#### Attachments

• excel.xlsx
10.3 KB · Views: 1
Last edited:
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.

#### Attachments

• Datedif.xlsx
11.7 KB · Views: 2