# IF vs. SUMIF? Or something else entirely?

#### Exceldiscipulus

##### New Member
Hello Everyone,
Here's what I'm trying to do. In worksheet "PopGrp4662," cell U34 gives a number of items I need to build. I want cell U66 to refer to worksheet "4662Stockpile" and check for excess of that item. If there is excess; good. I don't need to build that item and return a "0". If there isn't; bad this is how many I need build.
This the code I have so far.

=SUMIF('4662Stockpile'!C2:C115,T66,'4662Stockpile'!B2:B115)-U34
I've also Conditionally formatted the cell to display red font if the number is negative.

Rob

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi There

Sounds to me like IF function would be more appropriate to use.

=IF(A1<=B1, 0, A1-B1)

Where A1 is you stock quantity and B1 is the amount you should have. Edit accordingly. Hopefully I have the <= the right way round.

HTH

Dave

Hello Everyone,
Here's what I'm trying to do. In worksheet "PopGrp4662," cell U34 gives a number of items I need to build. I want cell U66 to refer to worksheet "4662Stockpile" and check for excess of that item. If there is excess; good. I don't need to build that item and return a "0". If there isn't; bad this is how many I need build.
This the code I have so far.

=SUMIF('4662Stockpile'!C2:C115,T66,'4662Stockpile'!B2:B115)-U34
I've also Conditionally formatted the cell to display red font if the number is negative.

Rob

Maybe...

=ABS(MIN(0,SUMIF('4662Stockpile'!C2:C115,T66,'4662Stockpile'!B2:B115)-U34))

which always yields what to build in positive mood, with 0 meaning of course "nothing to build".

Perhaps:

To return what to build as a positive number:
=-MIN(0,SUMIF('4662Stockpile'!C2:C115,T66,'4662Stockpile'!B2:B115)-U34)

To return what to build as a negative number:
=MIN(0,SUMIF('4662Stockpile'!C2:C115,T66,'4662Stockpile'!B2:B115)-U34)

Thankyou gentlemen, all of these did exactly what I wanted.

Rob

Replies
2
Views
2K
Replies
0
Views
440
Replies
13
Views
6K
Replies
0
Views
1K
Replies
0
Views
3K

1,221,153
Messages
6,158,231
Members
451,477
Latest member
CWebbers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back