IF vs. SUMIF? Or something else entirely?

[Exceldiscipulus]

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

 

[dave3009]

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]

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]

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)

 

[Exceldiscipulus]

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

Rob