Sumifs to roll up project hours?

Jackie Otte

I'm trying to sum up the total hours for a project based on a given date. However, the hours and project names are spread across 5 different columns for hours, and 5 different columns for project names. My Sumif comes up as Incorrect Argument. Do I need a combination of Countif and Sumif?

Example, I need to calculate the hours logged for the week of 8/31/2020, for the project named Reporting. Project Hours range is looking at all 5 Project hours columns and the CTE:Weekly Time Card Range 5 is looking at the 5 Project Names columns, looking for Reporting.

=SUMIFS({Project Hours}, {CTE: Weekly Time Card Range 5}, "Reporting", $Date@row)

David Tutwiler

I think you're going to have to take it one at a time going across and then add the results. The reason I'm thinking that is because SUMIFS just looks at a single range to do the summation. So, if I'm understanding your issue right, I think you'll need to do something like:

SUMIF#1 (is Project Name 1 Reporting? if so then sum Project 1 Weekly Hours)

+ SUMIF#2 (same thing for Project Name 2)

+ SUMIF #3 (etc, etc)

Does that make sense?

Jackie Otte

Yes that worked!! But now how do I limit that so it's only showing me hours for those projects for a specific date? So the formula below works, but when I try to throw in a date as a limiter, that's throwing the errors.

=SUMIF({Project Name 1}, "Admin", {Project 1 Hours}) + SUMIF({Project Name 2}, "Admin", {Project 2 Hours}) + SUMIF({Project Name 3}, "Admin", {Project 3 Hours}) + SUMIF({Project Name 4}, "Admin", {CTE: Weekly Time Card Range 1}) + SUMIF({Project Name 5}, "Admin", {Project 5 Hours})

Jackie Otte

I should also add, my Dates are not a Date column, they are listed out in a dropdown, so does that change the formula?

Genevieve P.

Hi @Jackie Otte

Were you able to resolve this issue?

Yes, if your dates are in a drop-down versus a date column this would affect your formula. DATE functions within formulas will only work if the column is a date-type, otherwise you may need to use a combination of the DAY/MONTH/YEAR functions to pull a date out of your text string, and then use that as criteria.

Jackie Otte

@Genevieve P I sort of got it resolved. I got the formula to work finally, but it would not work using a Date column, I had to change the whole column to a text format and then have it look for a specific date in order to work. I might try to play around with it more to see if I can get the Date column to work again

Jackie Otte

@Genevieve P So I've tried changing the Date column to an actual Date column instead of a text column. In my formula rollup sheet, I've also changed it to a Date column, but now my formulas don't work again.

Here's my formula

=SUMIFS({Project 1 Weekly Hour}, {Project Names 1}, @cell = [ADR Automation]$1, {Dates}, @cell = $Date2) + SUMIFS({Project 2 Weekly Hour}, {Project Names 2}, @cell = [ADR Automation]$1, {Dates}, @cell = $Date2) + SUMIFS({Project 3 Weekly Hours}, {Project Names 3}, @cell = [ADR Automation]$1, {Dates}, @cell = $Date2) + SUMIFS({Project 4 Weekly HOurs}, {Project 4 Names}, @cell = [ADR Automation]$1, {Dates}, @cell = $Date2) + SUMIFS({Project 5 Weekly Hours}, {Project 5 Names}, @cell = [ADR Automation]$1, {Dates}, @cell = $Date2)

Here's a couple screenshots, the first one below is my formula rollup sheet, where the specific project type is the column and I need to sum all hours for that project, for that specific date. The screenshot below is a layout of the data captured in our form for time tracking.

Genevieve P.

Hi @Jackie Otte

If I'm understanding you correctly, you have 5 sheets you need to add together, which is why you have 5 SUMIFS statements that you are using + to combine.

If this is the case, then currently you're using the exact same column for your final {Dates} reference, which will error since that's suck to one specific sheet.

You will need to have a new cross-sheet reference Per-Sheet for each of their individual Date columns...

ex:

=SUMIFS({Project 1 Weekly Hour}, {Project Names 1}, @cell = [ADR Automation]$1, {Project 1 Dates}, @cell = $Date2) + SUMIFS({Project 2 Weekly Hour}, {Project Names 2}, @cell = [ADR Automation]$1, {Project 2 Dates}, @cell = $Date2) + SUMIFS({Project 3 Weekly Hours}, {Project Names 3}, @cell = [ADR Automation]$1, {Project 3 Dates}, @cell = $Date2) + SUMIFS({Project 4 Weekly HOurs}, {Project 4 Names}, @cell = [ADR Automation]$1, {Project 4 Dates}, @cell = $Date2) + SUMIFS({Project 5 Weekly Hours}, {Project 5 Names}, @cell = [ADR Automation]$1, {Project 5 Dates}, @cell = $Date2)

Does that make sense?

Jackie Otte

@Genevieve P Not 5 sheets, 5 different project and hours columns all in 1 sheet. I have one sheet I use to capture all the data from the form, then I have a different sheet that I call my rollup sheet, where I"m calculating out hours for each specific project.

We have one form, where a user can submit hours for up to 5 projects. So in my rollup sheet, I have Project 1 Name, Project 1 Hours, Project 2 Name, Project 2 hours. Etc. So my formula has to look across all project hours, compare it to the project name, to try to sum up the totals for one specific project name. And then I repeat this formula for all the various project names to get a sum for each project, by a specific date.

Does that make sense?

Genevieve P.

Hi @Jackie Otte

Oh of course! My apologies.

In that case, there's nothing that sticks out to me as incorrect from your syntax... you could try removing the @cell = portion since that's not needed:

=SUMIFS({Project 1 Weekly Hour}, {Project Names 1}, [ADR Automation]$1, {Dates}, $Date2) + SUMIFS({Project 2 Weekly Hour}, {Project Names 2}, [ADR Automation]$1, {Dates}, $Date2) + SUMIFS({Project 3 Weekly Hours}, {Project Names 3}, [ADR Automation]$1, {Dates}, $Date2) + SUMIFS({Project 4 Weekly HOurs}, {Project 4 Names}, [ADR Automation]$1, {Dates}, $Date2) + SUMIFS({Project 5 Weekly Hours}, {Project 5 Names}, [ADR Automation]$1, {Dates}, $Date2)

If that doesn't help, we'll want to check each individual statement - is there only one of them that doesn't work, or do they all individually give you the same error?

If it's just one of the statements, I would double check the Cross Sheet references to ensure they're looking at the correct columns.

Let me know what you find out!