I want to find out the sum of a range within specific requirements based on salary.

jmtrix

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.

jmtrix

I figured it out! If I use the MONTH(@row) I can capture it for each column.

😁

John Jonassen

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)

jmtrix

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....

John Jonassen

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.

jmtrix

That is not really practical for a large list of resources that I would need to sum from - but thanks for trying.

jmtrix

I figured it out! If I use the MONTH(@row) I can capture it for each column.

😁