Formulas

Options

Hi everyone, 

I am back again  :(.  I am trying to figure out how to write a formula that will SUMIF column "Status" is "Active", column "Current Phase" is "Discovery", and column "Discovery Total Time" is populated with a number.  The last column will change as the phases are completed.  I have attached a screen shot for reference.

Formula that I thought might work but receive an error message is: =SUMIFS({Professional Services Tracking Range 1}, "Active", {Professional Services Tracking Range 6}, "Discovery", {Professional Services Tracking Range 7}, ">0")

Any help is greatly appreciated.

Thanks

transition.JPG

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you trying to count how many rows meet that criteria or are you trying to add the Discovery Total Time column, or...? I am also not sure why you are referencing a different sheet in your formula. Anywho...

     

    If you just wanted to count how many rows meet that criteria you can use this (substituting the column references for your sheet ranges if needed) (See first image for reference of formula in action):

     

    =COUNTIFS(Status:Status, "Active", [Current Phase]:[Current Phase], "Discovery", [Discovery Total Time]:[Discovery Total Time], >0)

     

    If you wanted to sum up the total number of hours for each row meeting all of that criteria you can use this (substituting the column references for your sheet ranges if needed) (See second image for reference of formula in action):

     

    =SUMIFS([Discovery Total Time]:[Discovery Total Time], Status:Status, "Active", [Current Phase]:[Current Phase], "Discovery", [Discovery Total Time]:[Discovery Total Time], >0)

     

    Hope that helps...

    Untitled.png

    Untitled2.png

  • Heather Dixon
    Options

    Thanks Paul...I am reference a this sheet in my formula because I was capturing the calculations on another sheet so that everything wasn't on the tracking sheet.  However, this formual works so I will just leave it the way it is and link the cells onto the other sheet.

     

    Thanks so much!

  • Heather Dixon
    Options

    Got it to reference the other sheet also laugh

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    AWESOME! Glad you got it working the way you needed. yes

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!