Formula Tweak

Options

Hi - How to I adjust (add to) this formula so it sums Green and Gray for Clark Usher?

=COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")



Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Clark Usher,

    You're halfway there!

    If your data range is not going to get any larger then the following formula works:

    =COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")+COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Gray").

    If you wanted it to be able to take in any future data being added, then remove the numbers in the range so the formula looks at only columns:

    =COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Green")+COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Gray").

    This is assuming you are only looking at Primary Reviewer - you would need to expand the formula further if also counting secondary reviewer.

    Hope this helps, if you have any questions please ask! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    Options

    Hi @Clark Usher,

    You're halfway there!

    If your data range is not going to get any larger then the following formula works:

    =COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Green")+COUNTIFS([Primary Reviewer]1:[Primary Reviewer]10, "Clark Usher", [Primary Status]1:[Primary Status]10, "Gray").

    If you wanted it to be able to take in any future data being added, then remove the numbers in the range so the formula looks at only columns:

    =COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Green")+COUNTIFS([Primary Reviewer]:[Primary Reviewer], "Clark Usher", [Primary Status]:[Primary Status], "Gray").

    This is assuming you are only looking at Primary Reviewer - you would need to expand the formula further if also counting secondary reviewer.

    Hope this helps, if you have any questions please ask! 😊

  • Clark Usher
    Clark Usher ✭✭
    Options

    Awesome, thought I was close, it was the + sign to add the nested countifs I was unsure of.

    Also, thanks for the further explanation on the data range!

    Cheers Clark

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!