formula question
Hi
I am struggling with a formula to calculate a number of fields and looking for some help.
I want to calculate the number of both estimated and actual hrs for each month for each client, I have tried the below, but I cannot see where I am going wrong, I have tried COUNTIFS also and cant get it to work.
=COUNTIF([Resolution Date]25:[Resolution Date]182, ">=01/02/24")  COUNTIF([Resolution Date]25:[Resolution Date]182, "<=28/02/24"),(COUNTIF([Actual Time Spent (Hrs)]25:[Actual Time Spent (Hrs)]182,">=0")COUNTIF([Actual Time Spent (Hrs)]25:[Actual Time Spent (Hrs)]182,"<=500"), COUNT(Client25:Client182,"Curd")
This is a excel snapshot example of what I have in Smartsheet  This will pull through to a metrics sheet when completed.
Thanks in advance for any advice on this one :)
Best Answer

Hi @Mel C
Since you need the total time spent, and not just the count of how many times Curd appeared in a certain time frame, you will need to use SUMIFS. Here's the formula to use for estimated hours in the month of Feb 2024.
=SUMIFS([Estimated Time Spent (hrs)25]:[Estimated Time Spent (hrs)]182, [Client]25:[Client]182, "Curd", [Resolution Date]25:[Resolution Date]182, >=DATE(2024, 02, 01), [Resolution Date]25:[Resolution Date]182, <=DATE(2024, 02, 29))
You can replace the name of the client for your other clients in the formula to get the total estimated time, replace Estimated time with Actual Time to get actual time spent, and change the dates to the starting and ending dates of different months to populate for the other months
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers

Hi @Mel C
Since you need the total time spent, and not just the count of how many times Curd appeared in a certain time frame, you will need to use SUMIFS. Here's the formula to use for estimated hours in the month of Feb 2024.
=SUMIFS([Estimated Time Spent (hrs)25]:[Estimated Time Spent (hrs)]182, [Client]25:[Client]182, "Curd", [Resolution Date]25:[Resolution Date]182, >=DATE(2024, 02, 01), [Resolution Date]25:[Resolution Date]182, <=DATE(2024, 02, 29))
You can replace the name of the client for your other clients in the formula to get the total estimated time, replace Estimated time with Actual Time to get actual time spent, and change the dates to the starting and ending dates of different months to populate for the other months
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!