• 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


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

Formula is not working in 1 of the following senario


New Member
Dear Excel Experts,

I am create logic to build a formula. But failed in 1 condition out of 5.Need your help.

I have below 5 scenarios
Sl NoScenariosRemarks
1Transaction_id and final_price2 is reflecting in the reportMatched
2final_price2 > Gross AmountDiscount Issue
3Transaction_id and final_price2 is not reflecting in the reportNot Matched
4Duplicate transaction ID with Negative and positive gross amount in the reportRefund/cancelled case
5Transaction pertains to the previous month/next monthNot considered

I am using this formula(=IF(TEXT(A2,"MMM")=$H$2,IF(C2=D2,"Matched",IF(D2="0","Not Matched",IF(AND(COUNTIF($B$2:$B$196,B2)>1,(C2<0)),"Refund",IF(AND(C2<D2,C2>-1),"Discount","Unknown Error")))),"Not Related to this month") )

But i am stuck in Refund case scenario where date is pertains to prior/next month. i am attaching sample data with formula.


  • Formula.xlsx
    16.4 KB · Views: 16
Here you go. I had to adjust the dates to US settings for me to get it work, but the biggest correction I made was IF(TEXT(A2,"MMM")<>$H$2,"Not Related to This Month",IF(C2=D2,"Matched",IF(D2="0","Not Matched",IF(AND(COUNTIF($B$2:$B$196,B2)>1,(C2<0)),"Refund",IF(AND(C2<D2,C2>-1),"Discount","Unknown Error"))))).

You want to make sure that if it doesn't pertain to the month, then that's the only thing you want it to do. And then everything else will follow if it does equal.


  • Formula (1).xlsx
    16.8 KB · Views: 2