LOOKUP VALUE with IF

[Keibri]

Hi guys,

I was wondering whether someone could help me with the below scenario which I am trying to look for solutions/alternatives.

For ease of reference I have attached a sample excel file.

For the sake of example, I have a cash management sheet which is recording the monthly cash inflows of commissions which are received by different individuals. Therefore each and every month, commission money is being transferred in as seen in column F.

After end of month, the amounts of commission which is received will be transferred to different bank accounts as seen in columns P:R. There will be 3 different transfers: commission bank account, petty cash account , or salaries account.

My focus is the commission. This has to be transferred within 7 days after month end. Therefore if month end is 28/02/2018 the exact amount of commission received for February will have to be transferred out by 07/03/2018.

The tricky part is that sometimes, an additional ad hoc transfer of commission might also be made within the same timeframe. Example as seen in February there were two transfers 386 and 20. The 386 is the exact amount of commission received in EUR terms. The 20 is a transfer which is done due to foreign exchange differences which might arise.

I would like to create a check in columns Z which would show whether the commission from previous month (e.g. if date is 07/02/2018, the commission of January amounting to 537) was transferred out or not by the deadline day. The formula I would like to create is that:
If in cell Y3, I have a date "07/02/2018" the formula would sum all the commission received for the month prior to that date i.e. from 01/01/2018 to 31/01/2018. Therefore 537. It would then lookup that 537 in column R. If the 537 is transferred by "07/02/2018" the formula should return the 537 if not a description saying "Commission hasn't yet been transferred".

Apologies for the long description.

Would really appreciate any ideas or help with the above

Many thanks

Keibri

[FDibbins]

I see no formulas or anything to show what money is for which account?

How do we know that on 2/1/18 something happens to -10 for Petty Cash?
Where are those values coming from, and based on what?

[Keibri]

Hi FDibbins,

Thanks very much for your reply!

For the time being there are no formulas created. Columns B:F will be raw data generated in excel which show all the commission received. Column P:R will also be raw date which shows all money transferred out.

In cell F102 there is an opening balance of 10,000. This is my initial capital. Each and every month commission is receiving and added with this capital.

However on a monthly basis there are also expenses: like salaries, or petty cash. These are transferred out from this account (they are in no way linked with commissions received but are just expense which occur during the month and which will be transferred to a separate account)

As you mentioned there are no formulas created, cause what you are seeing are 2 reports which will be raw data. That's why I would like to incorporate this check in cell Z3.

Much appreciated!!

[FDibbins]

Not sure how you ID the value in Z3, I can see that it = R6, but why isnt R8 also included?

Maybe try playing around with this, and see where you get?
=SUMIFS(R:R,P:P,">"&EOMONTH(Y3,-1),P:P,"<="&EOMONTH(Y3,0))
This sums ALL values in feb - you could add more criteria if you want

[Keibri]

Hi FDibbins,

Sorry for my late reply. Eventually I used some of your suggestions and manged to find a workaround Thanks a lot for your help!

[FDibbins]

happy to help and thanks for the feedback