CODEWITHSUNDEEP
excel
PENNY
PENNY

Reputation: 1

Excel formula ( combination of IF and Sum )

I am trying to create a formula for Column C as shown below. I am trying to identify
(1) if any of the ITEM contains the word RED ( or the value in cell B1), THEN
(2) capture the value in column C and sum it up.
(3) If the value sums up less than 10,000 (or the value in cell B2), then it will return the word "OK". Otherwise, return the word "STOP".

Side Note : There will be continuous input in Column A and each time when I key in new item that contains the word red AND quantity value adds up the figures above whether greater or lesser than 10,000, it will show me either "OK" or "STOP".

enter image description here

Upvotes: 0

Views: 455

Answers (2)

user3819867
user3819867

Reputation: 1120

=IF(AND(SUMIFS($B$5:$B5,$A$5:$A5,"*RED*")>$B$2,NOT(ISERROR(VLOOKUP("*RED*",$A5,1,0)))),"STOP","OK")

For practical reasons I would switch things and put a list of colors in there, first identify the corresponding color then the corresponding limit number then match the running total with said limit.
Confer link to find a proper solution.

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

Put in C5:

=IF(ISNUMBER(SEARCH($B$1,A5)),IF(B5<=$B$2,"OK","STOP"),"")

And in C6:

=IF(ISNUMBER(MATCH("STOP",$C5:C5,0)),"",IF(ISNUMBER(SEARCH($B$1,A6)),IF((SUMIF($C$5:C5,"OK",$B$5:B5)+B6)<=$B$2,"OK","STOP"),""))

Then simply copy down from C6 as you need it. But Keep in mind that a key word like "as" will also be found if the word "base" is in the ITEM.

Upvotes: 0

Related Questions