Please Help! Excel Formula needed!Solved/Closed

Question

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


Configuration: Windows XP / Internet Explorer 7.0

Ray · Answer

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.

Ray · Answer

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
.

Please Help! Excel Formula needed!

2 responses

Similar discussions

Subscribe To Our Newsletter!