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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
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,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,710
Members
410,631
Latest member
JFORTH97
Top