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