I have the following headings on a spreadsheet that is to follow cash flow -

Column F is the rate

Column I is 25% deposit of the rate in F

Column N is total extras

Column O is Balance amount

Column P is date balance received

Column Q is total revenueI firstly asked the question of how to include a formula so that Column O gave a negative amount if a date wasn't put in column P.

The problem I now have is the total revenue is taking these negative figures into account and not giving a true total revenue running reading.

The second problem is the total revenue is taking into account the negative balance amount in column O, even if a deposit has been paid?

Is there a formula to rectify this problem?

Any help greatly received, or a further explanation can be given,Regards, Ian

Hi, Column Q on each row can use this formula to show zero if no balance paid or to show the balance paid once the column P cell (date balance received) has been completed:

=IF(P2="",0,O2)To total column O (balance amount) for all rows use this:

=SUMIF(O2:O10,">0",O2:O10)

(adjust row numbers as appropriate)

This only sums positive amounts.To find total of outstanding balances use this:

=SUMIF(O2:O10,"<0",O2:O10)

This only sums negative amounts(I am assuming that the amount in column O changes from negative to positive when column P is completed).

Regards

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History