SOLVED

sumif simple budget sheet

New Contributor

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.

 

DayDescriptionLast Month AmtThis Month Amt
    
2ndBoost59.0059.00
2ndBoost35.0035.00
3rdSignature200.00200.00
 Microsoft1.99 
3rdAmazon Music7.99 
    
13thMercury CC 901.24
13thMerrick CC 1116.13
9thBalance of Nature69.95 
9thForemost Insurance162.57 

 

 

I thought this was going to be really simple.

 

6 Replies

@larksys That could be something like this:

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

Unless I misinterpreted your intentions. 

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.

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

@Riny_van_Eekelen 

 

     A               B                                             C                             D

DayDescriptionLast Month AmtThis Month Amt
    
2ndBoost59.0059.00
2ndBoost35.0035.00
3rdSignature200.00200.00
 Microsoft1.99 
3rdAmazon Music7.99 
    
13thMercury CC 901.24
13thMerrick CC 1116.13
9thBalance of Nature69.95 
9thForemost Insurance162.57 

 

 

                                                                        should get                2553.87

 

corrected formula for this example;

 

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

@larksys 

Riny_van_Eekelen_0-1662995274692.png

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
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.