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?

Billable Hours.PNG

Find more posts tagged with

Education

Formulas

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…