• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

(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

NameYYYYMMDDAlert 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
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.

83316
 

Attachments

  • Datedif.xlsx
    11.7 KB · Views: 2
Back
Top