Log In
Register
Home
December 22, 2018
Offline
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
See AnswerSeptember 9, 2020
Offline
Hi Aye,
Here is a possibility with formula.
BR,
Lionel
September 9, 2020
Offline
And with Power Query (For the first two months).
Answers Post
December 22, 2018
Offline
Many thanks, Lionel,
It is indeed a great help.
Regards,
Aye
December 22, 2018
Offline
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
1 Guest(s)
