if and formula not working

Hi,

looking for help on this formula

I would like for two conditions to be met for the total contract hours value to count towards the sum of the column.

so if the PIP type is equal to "CE PIP AC" and the status ball is green then the hours for that row should count in my sum.

I hope that makes sense.

=IF(AND([PIP TYPE]@row = "CE PIP AC"), [STATUS]@row = "GREEN"), =SUM(TOTAL CONTRACT HOURS:TOTAL CONTRACT HOURS)

Any help/guidance is appreciated. Thank you in advance.

Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 01/03/24

    Hello @LisaPetersonBW

    Does this formula work for you?

    =IF(AND([PIP TYPE]@row = "CE PIP AC"), [STATUS]@row = "GREEN"), SUM([TOTAL CONTRACT HOURS]:[TOTAL CONTRACT HOURS)]

    This formula will populate "GREEN" if "CE PIP AC" is found, otherwise it will SUM the Total Contract Hours Column.

    https://www.linkedin.com/in/zchrispalmer/

  • Hi Chris

    Thank you for your reply, however, the formula is not working. it's not a green status ball it is a drop down Yes, No.

    Sorry for the confusion.

    Would I replace the "green" with "yes".

    Also, I am hoping to have the cell to reflect the total contracts hours when the values in the status column are "Yes" and the "CE PIP AC".

    I appreciate your help

    Lisa

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!