COUNTIFS Question

Amy_API

I'm having an issue understanding COUNTIFS in my scenario. 

I'm trying to track billable hours, and I'd like to summarize the hours value listed if another column equals a specific value. So I can track how many hours go to which projects. I believe COUNTIFS is the right formula to use, but not sure how to apply it. Any advice would be great!

Also how many fields deep can a countifs formula go?

Mike Wilday

I think you might be wanting a SUMIFS formula. To sum the number of hours based on the task? Is that correct? 

=Sumif(Task:Task, "Planning", [Time Spent (Hours)]:[Time Spent (Hours)])

Will give you a sum of all the hours for tasks titled as Planning.

You will need to summarize each type of task in a separate formula that you want to summarize. One formula for planning and one for Sourcing. Does that make sense? 

Countifs will give you a 1 count for each time your criteria appears. 

This post can help you understand the countifs formula...  https://help.smartsheet.com/function/countifs

Basically, you pick a range to search and declare the criteria. one after the other... =Countifs(Range1:Range1, Criteria 1, Range2:Range2, Criteria 2) to sum one column based on multiple criteria. Each of the formulas only provide one count. If you want separate counts, one for Sourcing and One for planning 

Amy_API

Yep you were right, I realized right after I posted I needed SUMIF.

I also think the parentheses in the header might have thrown something off in the formula so I changed the name to Hours Spent. But all is working now!

One more question If I'm trying to go one layer deeper and want to find out the sum hours if 2 conditions are met. Let's say course = PR and task=planning how would I modify the formula? I took a shot at it but got an #incorrect augment set Error.

=SUMIF([Course Name]:[Course Name], "PR", Task:Task, "Planning", [Hours Spent]:[Hours Spent])

Thank you!!

Mike Wilday

In that instance you'll want to use sumifs  https://help.smartsheet.com/function/sumifs

But the formula is set up a little differently.

=Sumifs(Range to Sum:Range to sum, [Criterion Range to check]:[Criterion Range to check], "Criteria", [Criterion Range to check2]:[Criterion Range to check2], "Criteria2", 

Amy_API

Perfect, it worked for me. Thanks for clearing this up!! 

Mike Wilday

Glad I could be of service.