Need to verify SUMIFS formula - Incorrect arrgument set

Options

=SUMIFS([Diagnostic]:[In Progress], 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022)


Want to sum values in columns Diagnostic, Disc and In Progress where the date/year is 2022.

The columns are in this order - Diagnostic, Discovery and In Progress. With 1 or 0 values in them.

I need a sum of all these 3 columns where the year is 2022 - which will give me a total number.

Best Answer

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

    Hi @keesuri25

    I hope you're well and safe!

    It doesn't work because it's not in the same range. You're comparing three columns against one. You'd have to calculate them one by one and add them together.

    Try something like this.

    =SUMIFS(Diagnostic:Diagnostic, Diagnostic:Diagnostic, 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS(Disc:Disc, Disc:Disc, 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS([In Progress]:[In Progress], [In Progress]:[In Progress], 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022)

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

Answers

  • keesuri25
    Options

    =SUMIFS([Diagnostic]:[In Progress], [Diagnostic]:[In Progress], 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022) - doesnt help either.


    The range and range criteria is the same here. Or do i explicitly break it by column name. ?

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

    Hi @keesuri25

    I hope you're well and safe!

    It doesn't work because it's not in the same range. You're comparing three columns against one. You'd have to calculate them one by one and add them together.

    Try something like this.

    =SUMIFS(Diagnostic:Diagnostic, Diagnostic:Diagnostic, 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS(Disc:Disc, Disc:Disc, 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS([In Progress]:[In Progress], [In Progress]:[In Progress], 1, [Start Date]:[Start Date], IFERROR(YEAR(@cell), 0) = 2022)

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    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.

  • keesuri25
    Options

    Thank you so much Andree.. That worked.

    Yes I was thinking to break the columns and calculate one by one.. Got it.. Thank you again. Have a great day.

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

    @keesuri25

    Excellent!

    Happy to help!

    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!