Sumifs to roll up project hours?

Options

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)


Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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
    Options

    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
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    @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
    Options

    @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.
    Genevieve P. Employee Admin
    edited 12/10/20
    Options

    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
    Options

    @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.
    Genevieve P. Employee Admin
    edited 12/10/20
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!