I want to find out the sum of a range within specific requirements based on salary.
I am looking to seek the capacity of resources using billable hours each month. For example, all resources that were available in February - what was the total capacity for billable hours, and so on for each month. I have the start and end dates in two separate columns with the sum of billable hours available and a column showing if they are billable or not. Here is what I am using for the month of February - but don't think this is correct as it is not giving me the correct sum:
=SUMIFS({sumrange}, {enddate}, <=DATE(2021, 2, 28), {startdate}, >=DATE(2021, 1, 1), {billable}, >0)
Most resources are noted as start at 01 Jan and finish 31 Dec - -but some resources will be starting in March or departing mid month - so I need to work out their billable capacity.
Best Answer
-
I figured it out! If I use the MONTH(@row) I can capture it for each column.
😁
Answers
-
Because of the way that your set up seems to be done, based on how you have described, you may need to change the approach of how your billable hours are entered, might need to create a row for each month for each resource that is potentially going to be there for the full year.
So rather than showing
Resource 1234 | StartDate 1/1/2021 | EndDate 12/31/2021
You would do this
Resource 1234 | StartDate 1/1/2021 | EndDate 1/31/2021
Resource 1234 | StartDate 2/1/2021 | EndDate 2/28/2021
Resource 1234 | StartDate 3/1/2021 | EndDate 3/31/2021 (and so on)
-
Not sure that makes sense to me. I have the sheet with all the resource information and am using the month summary of billable capacity by month - for example:
Column1 Column2
January =sumifs(with formula {referencing cell from other sheet} to determine month's capacity)
February and so on....
-
I don't know how to better explain...but I will try. HA
On the sheet with all the resource information, each resource that can provide billable time across a multiple month date range, you would need to have a row for that 1 resource in each month that they will touch. That is the only way (that I can think of) that you can get a true and complete billable capacity by month.
Example. John Smith Starts on January 1, 2021 and Ends December 31, 2021.
Instead of doing this:
John Smith | StartDate 1/1/2021 | EndDate 12/31/2021
Your resource information sheet would run like this (repeating from my previous reply):
John Smith | StartDate 1/1/2021 | EndDate 1/31/2021
John Smith | StartDate 2/1/2021 | EndDate 2/28/2021
John Smith | StartDate 3/1/2021 | EndDate 3/31/2021
Looking at both the Start and End Dates as criteria in your monthly Summary's formula, you are completely eliminating those that run through multiple months.
-
That is not really practical for a large list of resources that I would need to sum from - but thanks for trying.
-
I figured it out! If I use the MONTH(@row) I can capture it for each column.
😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!