Please Help! Excel Formula needed!

[Solved/Closed]
Report
-
 Ray -
Hello,

I have a spreadsheet with many columns: date issued, invoice number, gross£, net£, vat£, date due, date paid.

until the invoice is paid the ''date paid'' column is blank.

What i would like to do is have a figure that shows the 'gross£' owed - ie: sum of all the invoices that have the 'date paid' cell is blank then add 'gross£' to a cell.

I hope this makes sense and that you can help me out

Many thanks

:)

Felloutthesky


2 replies

date issued invoice no gross net vat date due date paid total outstanding
8/1/2010 aaa 15 10 5 9/1/2010 8/30/2010 45
8/2/2010 bbb 15 10 5 9/2/2010 8/30/2010
8/3/2010 ccc 15 10 5 9/3/2010 9/1/2010
9/3/2010 ddd 30 20 10 10/3/2010
9/4/2010 eee 30 20 10 10/4/2010
9/5/2010 fff 30 20 10 10/5/2010
9/6/2010 fff 30 20 10 10/6/2010

The formula for 'total outstanding" is: =SUMIF(G2:G8,">0",C2:C8)
Am I close? I didn't add the gross back in as I wasn't sure what you meant.
I was wrong. The total outstanding should be 120. Use this instead: {=SUM(IF(G2:G8,"=0",C2:C8))}
That's an array. Type in the bit between the curly brackets and press CTRL-SHIFT-ENTER instead of just ENTER
.