Hi Team,
I just want to make my COUNTIFS formula (COUNTIFS($B9,"<="&D$1,$C9," ")+COUNTIFS($B9,"<="&D$1,$C9,">= " & D$1) work for the following scenario.
If AccomEntryDate is earlier than D1(nominated EOM), and DepartureDate is "null", it will be 1.
If AccomEntryDate is earlier than D1(nominated EOM), and DepartureDate is after D1(nominated EOM) , it will also be 1.
if both conditions are not met, it will be 0
Please see attached working data and ExpectedResult for the reference.
If you can direct me to Power query, with the similar formula, that will be better.
Many thanks.
Regards,
Aye
Hi Aye,
Here is a possibility with formula.
BR,
Lionel
And with Power Query (For the first two months).
Many thanks, Lionel,
It is indeed a great help.
Regards,
Aye
Hi Lionel,
I just have to add some more additional columns on my post.
I added ReceivedDate, Full/Partial and Amount columns for the same customerID(CustID). I now have change my formulae to ReceivedDate instead of EntryDate.
I cannot have duplicated records in my expected result.
In our formulae, we have to look if the clients are the same and received date are different, it will take the latest date at the specified month. Can you please guide me to fix the formula for both excel and power query.
Many thanks.
Regards,
Aye
Hello Aye,
I' m joining you the file with a formula. Warning, it' s a matrix formula to be validated with CTRL SHIFT ENTER.
