Formula Error - Sumifs

Options
Gordon
Gordon ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hello,

I'm trying to build out a SUMIFS formula that adds up timesheet data from a separate data sheet using 4 criteria:  employee name, project code, start date and end date.  The formula works fine if I don't have criteria for project code.  As soon as I add another criteria to match the client code, I get an #INCORRECT ARGUMENT SET error.

Here's the formula I wrote:

=SUMIFS({Timesheet Detail Hours}, {Timesheet Detail Client Code}, [Client Code]@row, {Timesheet Detail EE Number}, [Employee No]@row, {Timesheet Detail Date}, >=[Start Date]@row, {Timesheet Detail Date}, <=[End Date]@row)

The external Timesheet detail sheet being referenced is a flat data table.  The client code is a 12-character alphanumeric code consisting of ABC001.ABC01 format, except certain records (rows) have client codes in the external sheet formatted as ABC001.0

Any help or insights on what I may be doing wrong is greatly appreciated.

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi Gordon,

    Are all ranges the same length?

    More info about the error:

    #INCORRECT ARGUMENT SET

    Cause

    This error is presented under the following circumstances:

    1. For functions that take two ranges: The range sizes don’t match for the function.
    2. The function is missing an argument.
    3. There is an extra function in the argument.

    Resolution

    Correct the range size or arguments, adding or removing arguments in the formula.

    Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Gordon
    Gordon ✭✭✭✭✭
    Options

    Yes, I believe all the ranges are the same length, since when I created the external references, they were to the entire column in the external sheet.

    However, I just opened the sheet with the SUMIFS formula again this morning and the error is gone.  I didn't do anything to the external sheet.  Weird...  surprise

    Thanks!!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Happy to help!

    Glad to hear that it works now!

    A temporary glitch maybe, it happens!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!