COUNTIFS Question
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?
Comments
-
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
-
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!!
-
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",
-
Perfect, it worked for me. Thanks for clearing this up!!
-
Glad I could be of service.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!