I want to use a conditional sum function on a budget spreadsheet.

Eg: I have a food budget of £333 per month. What I want to do is track
any surplus that accrues from month to month if I underspend, and any
deficit if I overspend.

I want this amount to be recorded cumulatively in a cell marked
"surplus".

So if I put the budgeted amount (in this case £333) in C9, and then
record actual spends for April, May, June in C10, C11 and C12, I want
to put the difference between the sum of the actual spends and the
total budget (in this case 3*333) in the "surplus" cell, say C13.

What I need is for the formula to only perform this calculation if
there is an actual spend recorded for that month. If it isn't
conditional in this way then it calculates it for months in the
future, treats empty cells as zero actual spends and screws up the
amount in the surplus cell.

Can anyone help with this?

Thanks
Gareth

Using your example data:

=COUNTA(C10:C12)*C9-SUM(C10:C12)

HTH

" wrote:

I want to use a conditional sum function on a budget spreadsheet.

Eg: I have a food budget of £333 per month. What I want to do is track
any surplus that accrues from month to month if I underspend, and any
deficit if I overspend.

I want this amount to be recorded cumulatively in a cell marked
"surplus".

So if I put the budgeted amount (in this case £333) in C9, and then
record actual spends for April, May, June in C10, C11 and C12, I want
to put the difference between the sum of the actual spends and the
total budget (in this case 3*333) in the "surplus" cell, say C13.

What I need is for the formula to only perform this calculation if
there is an actual spend recorded for that month. If it isn't
conditional in this way then it calculates it for months in the
future, treats empty cells as zero actual spends and screws up the
amount in the surplus cell.

Can anyone help with this?

Thanks
Gareth

Toppers that's it - excellent, thanks a lot!

Gareth

On 17 Apr, 20:18, Toppers wrote:
Using your example data:

=COUNTA(C10:C12)*C9-SUM(C10:C12)

HTH

" wrote:
I want to use a conditional sum function on a budget spreadsheet.

Eg: I have a food budget of £333 per month. What I want to do is track
any surplus that accrues from month to month if I underspend, and any
deficit if I overspend.

I want this amount to be recorded cumulatively in a cell marked
"surplus".

So if I put the budgeted amount (in this case £333) in C9, and then
record actual spends for April, May, June in C10, C11 and C12, I want
to put the difference between the sum of the actual spends and the
total budget (in this case 3*333) in the "surplus" cell, say C13.

What I need is for the formula to only perform this calculation if
there is an actual spend recorded for that month. If it isn't
conditional in this way then it calculates it for months in the
future, treats empty cells as zero actual spends and screws up the
amount in the surplus cell.

Can anyone help with this?

Thanks
Gareth