Formulas

Heather Dixon

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

Paul Newcome

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...

Heather Dixon

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

Got it to reference the other sheet also 

 

 

Paul Newcome

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