Sum Actual and Projected Labor Across Multiple Sheets

RJT

Good Afternoon Community. I am currently trying to sum up actual and projected labor hours across multiple sheets and broken down by month. Below is current formula I am trying but it does not work.

The three sheets I am trying to sum the data are:

• Admin Bldg
• F&B Warehouse
• Tech Warehouse
  

=SUM({P931 - Admin Bldg - Walkthrough Actual}, {P931 - F&B Warehouse - Walkthrough Actual}, {P931 - Tech Warehouse - Walkthrough Actual}, AND(@cell <= DATE(2024, 1, 31), @cell >= DATE(2024, 1, 1))))

When I use the below I am able to get the sum but I cannot break it down by month.

=SUM({P931 - Admin Bldg - Walkthrough Actual}, {P931 - F&B Warehouse - Walkthrough Actual}, {P931 - Tech Warehouse - Walkthrough Actual})

I appreciate any assistance that can be provided.

SoS | Dan Palenchar

Hello @RJT,

First off, you need to use SUMIF() or SUMIFS() rather than SUM(). SUM() does not allow you to sum only rows that meet a condition, like being in between dates.

Second, you need to query a date column within your SUMIF() or SUMIFS(). In other words, you need a Criterion Range that is a Date column and then your criterion can be your existing DATE() function.

However, If you are looking across a range of dates and want to count based on Month I would suggest adding a new column in your Source (Data) Sheets called Month. And input the formula =MONTH([Whatever Date Column You Care About]@row)

This will give each row a 1-12 number with the month that date is in. I would then set up a Metric Sheet and input numbers 1-12 in individual rows in a column called Month.

Then, in the Metric Sheet I would add columns for each column of each Sheet you want to include in your monthly sum, for example P931 - Admin Bldg - Walkthrough Actual. In this column I would use this formula:

=SUMIF({Month Column on Other Sheet}, month@row, {P931 - Admin Bldg - Walkthrough Actual})

I would repeat this formula in a new column for each sheet.

You can add a final column to get the total sum by using a SUM() function that references all of the columns in the row.

Hope this helps!