formula question

Mel C

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 :-)

AravindGP

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

AravindGP

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