Find amount under budget.

**azland00123** · 05-07-2012, 10:02 AM

I know this should be simple but I have not had any luck. I need to find the amount that I am under budget. The problem is that other fields in the column are messing with the total. I know there is a way to get this done without changing the formula each time but cant find out how. Probably because I don't know how to ask the question. I am uploading a sample worksheet. The number in green is what I want. The only problem is, I have to change the formula each month. Any help would be greatly appreciated.

**martindwilson** · 05-07-2012, 10:13 AM

one way
=SUMIF(B4:B17,"<>"&"",D4:D17)

**Fotis1991** · 05-07-2012, 10:15 AM

Second way...

=SUMIF(B4:B15,">0",D4:D15)

**azland00123** · 05-07-2012, 10:28 AM

Thanks to both of you it works great both ways. I knew it would be easy. I have one question though. When we do this formula How does it know that we only want to sum the ones that are greater then zero. It looks like it should say don't sum any because some of them are not true. And I don't understand what the & sign is doing. <>"&"

**Fotis1991** · 05-07-2012, 10:37 AM

=SUMIF(B4:B15,">0",D4:D15)

Formula "says". Add all the values in range d4:d15, IF the values in the same row in range B4:B15, are biggest than 0.

=SUMIF(B4:B15,"<0",D4:D15)

This one "says" to add the less than 0 values.

Sorry for my poor English.

**martindwilson** · 05-07-2012, 11:12 AM

<> means ,does not =

**azland00123** · 05-07-2012, 01:03 PM

"does not = "&" What does "&" do? Thanks Bob

**martindwilson** · 05-07-2012, 01:18 PM

it says "<>"&"" ie doesnot = blank..
& joins the 2 bits together ,its just the way it's done in sumif
=SUMIF(A1:A10,"<>""",B1:B10) doesnt work with "" which is text so we use =SUMIF(A1:A10,"<>"&"",B1:B10)
but either work with a number
=SUMIF($A$1:$A$10,"<>0",$B$1:$B$10)
=SUMIF($A$1:$A$10,"<>"&0,$B$1:$B$10)