IF vs. SUMIF? Or something else entirely?

Exceldiscipulus

New Member
Joined
Sep 18, 2006
Messages
3
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.

Thanks for your help,
Rob
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.

Thanks for your help,
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".
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,140,937
Messages
5,703,264
Members
421,289
Latest member
fbohlandt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top