SOLVED

New Contributor

# sumif simple budget sheet

I have a sheet that has column C as last months expenditure and column D as this months expenditure.  As I progress through the month I want an idea of how much I need to get to the end of the month.  If column D is zero I want to assume the value in column C.

=SUMIF( D15:D44,"=0",C15:C44 )

The result is 0.00.

 Day Description Last Month Amt This Month Amt 2nd Boost 59.00 59.00 2nd Boost 35.00 35.00 3rd Signature 200.00 200.00 Microsoft 1.99 3rd Amazon Music 7.99 13th Mercury CC 901.24 13th Merrick CC 1116.13 9th Balance of Nature 69.95 9th Foremost Insurance 162.57

I thought this was going to be really simple.

6 Replies

# Re: sumif simple budget sheet

@larksys That could be something like this:

``=SUM(D15:D44)+SUMIF(D15:D44,0,C15:C44)``

Unless I misinterpreted your intentions.

# Re: sumif simple budget sheet

The result is correct for =sum(d15:d44) but the rest of it does not substitute the C column if the D column is zero.

# Re: sumif simple budget sheet

@larksys Please clarify/show how the end result should be.

# Re: sumif simple budget sheet

A               B                                             C                             D

 Day Description Last Month Amt This Month Amt 2nd Boost 59.00 59.00 2nd Boost 35.00 35.00 3rd Signature 200.00 200.00 Microsoft 1.99 3rd Amazon Music 7.99 13th Mercury CC 901.24 13th Merrick CC 1116.13 9th Balance of Nature 69.95 9th Foremost Insurance 162.57

should get                2553.87

corrected formula for this example;

=SUM(D3:D12)+SUMIF(D3:D12,"=0",C3:C12)

# Re: sumif simple budget sheet

Almost the same what I had earlier. Replace the 0 with "". Just make sure the empty cells in D are really empty.

best response confirmed by larksys (New Contributor)
Solution

# Re: sumif simple budget sheet

There must be a difference between your Excel release and my 365 release.

=SUM(D3:D12)+SUMIF(D3:D12,"",C3:C12) does not work

but;

=SUM(D3:D12)+SUMIF(D3:D12,0,C3:C12) works if I enter a zero in those blank cells. That's not quite good enough just for data entry sake. I formatted that column as numeric with 2 decimal places but that didn't do anything. Anyway, you did a good job of trouble shooting the formula.