Sumifs to roll up project hours?

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)

Find more posts tagged with

Accepted answers

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.