Automated Check box when a sum of cells is greater than another cell

arose
arose ✭✭✭
edited 03/08/24 in Formulas and Functions

I currently have an IF statement that will check a box when one column is greater than another column. However, I need to update the formula to allow check off the box based on the sum of 2 cells. My current formula is =IF(CE56 >= [Required CE]@row, 1, 0). Where the "CE56" is just a specific cell in the column called CE.

I need a check box to be checked off when the sum of cells "CE" and "Non CEs" is equal to or greater than the cell "required CE". I have tried a couple things but I'm not very good with the formulas in smartsheet. Can someone help?

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Then you can use the second suggested logic:

    (CE@row + [Non-FMI CEs]@row) >=[Required CE]@row
    

    (I have swapped the logic after noticing the edit to your comment above. I had assumed "checked" meant checked and "checked off" meant unchecked but it now looks like it means "checked" as well.)

    You put that logic into your IF statement turning this:

    =IF(AND(CE56 >= [Required CE]@row, extra bit of logic),1, 0)

    Into:

    =IF(AND(CE56 >= [Required CE]@row, (CE@row + [Non-FMI CEs]@row) >=[Required CE]@row),1, 0)
    
    

    The box will be checked if CE56 is greater than or equal to Required CE and CE and Non-FMI CEs together is also greater than or equal to Required CE.

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Can you explain sum of cells "CE" and "Non CEs"?

    Is this the sum of the column CE and the sum of the column Non CEs?

    Which would make the extra bit of logic we need:

     (SUM(CE:CE) +SUM([Non CEs]:[Non CEs]) )<[Required CE]@row
    

    Or is this a field in the row such as Required CE?

    Which would make the extra bit of logic we need:

    (CE@row + [Non CEs]@row) <[Required CE]@row
    

    Or is it something entirely different?

    When we figure that out we can add an AND to the IF

    =IF(AND(CE56 >= [Required CE]@row, extra bit of logic),1, 0)

  • arose
    arose ✭✭✭
    edited 03/25/24

    Hi, the CE and Non CEs columns will have their own values per row, so I need the logic to check the box if CE + Non CEs is equal to or greater than the required CE value for each row

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Then you can use the second suggested logic:

    (CE@row + [Non-FMI CEs]@row) >=[Required CE]@row
    

    (I have swapped the logic after noticing the edit to your comment above. I had assumed "checked" meant checked and "checked off" meant unchecked but it now looks like it means "checked" as well.)

    You put that logic into your IF statement turning this:

    =IF(AND(CE56 >= [Required CE]@row, extra bit of logic),1, 0)

    Into:

    =IF(AND(CE56 >= [Required CE]@row, (CE@row + [Non-FMI CEs]@row) >=[Required CE]@row),1, 0)
    
    

    The box will be checked if CE56 is greater than or equal to Required CE and CE and Non-FMI CEs together is also greater than or equal to Required CE.

  • arose
    arose ✭✭✭

    Thank you much appreciated!

  • KPH
    KPH ✭✭✭✭✭✭

    Good to hear. Glad I could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!