Forum Discussion

janetm126's avatar
janetm126
Brass Contributor
Jun 03, 2022
Solved

Formula Trouble

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 a...
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 03, 2022

    janetm126 

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

    You can try this formula.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 03, 2022

janetm126 

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

You can try this formula.

 

janetm126's avatar
janetm126
Brass Contributor
Jun 07, 2022
OliverScheurich, 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)?
    • janetm126's avatar
      janetm126
      Brass Contributor
      Jun 08, 2022
      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?
    • janetm126's avatar
      janetm126
      Brass Contributor
      Jun 08, 2022

      OliverScheurich 

      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?

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Jun 08, 2022

        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.

         

Resources