Formula Trouble

[janetm126]

I have a range of cells (C5:C31) in which a numeric value can be placed.  If 3 or more of these cells contain >=20, I want another cell to return "Warning".  I am far from an expert, but I've tried all I can do to figure it out.  Would someone please tell me how to write this dang formula? Thank you!

[#2 · Accepted solution]

@janetm126 

=IF(COUNTIF($C$5:$C$31,">="&20)>=3,"Warning","")

You can try this formula.

 

[#3]

>3! Thank you! I could not figure out how to add that in! I appreciate you so much.

[#4]

@Quadruple_Pawn, what if I needed an additional warning on top of the warning I already have? If C1:C31 >=100 AND E14=Warning, return Super Warning in another cell. How can I say IF AND when I am relying on the results of another formula (E14)?

[#5]

@janetm126 

Does the formula in the attached file return the expected result?

[#6]

@Quadruple_Pawn 

Not quite.  I am tracking monetary losses for a month's time and the disciplinary action for each loss category: cumulative amounts, large amounts, and subsequent large amounts. C5:C35 represents the 1st - 31st.

Employee receives disciplinary action for losses > 20*3 in the month.  Cell 47 has the formula you provided (thank you!): =IF(COUNTIF($C$5:$C$35,">="&20)>=3,"Coach","")

Employee receives disciplinary action for a any loss of > 250. Cell C48 has the formula:  =IF(COUNTIF($C$5:$C$35, ">="&250)>=1, "Warning", " ")

So, now, if the employee has the "Warning" in cell C48 AND they have another loss of > 100, I need cell C49 to return "Super Warning."  I hope these details make my request clearer.

P.S. Column C is for January.  I need the formulas to work for every other month as well.  Will the $ in my existing formulas cause issues since it's anchored?

[#7]

Tried your formula again and it does work! But it only references one cell being >= 100. How can I make it apply to C5:C35?

[#8]

@janetm126 

=IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning","")

This is the formula in cell C49 for january. I've changed the $ references to C$5:C$35 and copied the formula across the range C49:N49 for all months.

The formulas for "Coach" and "Warning" are adapted in the same way in the attached file.

[#9]

You are AMAZING!!!!!

[#10]

So, let me ask you one last thing. If I have... =IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning","")
...and I change the >=2 to =1 (so only one additional 100 loss gets the super warning), can I add in another logical test that says if a second 100 loss occurs (>=2?) returns "Youre Out"?

[#11]

@janetm126 

The >=2 in the "Super Warning" formula means that there is only one additional 100 loss (at least). This is because of the 250 loss from the "Warning" formula. This 250 loss is at the same time a 100 loss. Another 100 loss would mean >=2 in the "Super Warning" formula. I'd use >=2 instead of =2 in order to return the "Super Warning" for any number of additional 100 losses.

=IF(AND(C$48="Warning",COUNTIF(C$5:C$35,">="&100)>=3),"You're out","")

I've entered this formula for "You're out" in the attached file.

For verification i've entered losses of 252, 117 and 125 step by step for december in order to check the results of the formulas and it seems to work (i didn't save these entries).

I don't want to complicate things but i was wondering if you want to return any message if there are e.g. 3 losses of 100 and none of these is a 250 loss. 

[#12]

@janetm126 

=IF(AND(COUNTIF(C$5:C$35,">="&250)>=1,COUNTIF(C$5:C$35,">="&100)>=3),"You're out",IF(AND(COUNTIF(C$5:C$35,">="&250)>=1,COUNTIF(C$5:C$35,">="&100)>=2),"Super Warning",IF(COUNTIF(C$5:C$35,">="&250)>=1,"Warning",IF(COUNTIF(C$5:C$35,">"&20)>3,"Coach",""))))

I've combined all formulas in cell C52 and copied across range C52:N52.

[#13]

That is certainly a flaw in the "policy" as I should have a message for that too!

[#14]

You deserve to be Quintuple_Pawn or Quadruple_King for all your help. You have been amazing. I hope to show you my skill set in the future as I get better and better.