SUMIFS #INCORRECT

I have a formula referencing an external sheet's 4 columns that works fine:

=SUMIFS({Name 2022 - Travel To Site}, {Name 2022 - Travel Site To Site}, {Name 2022 - Travel From Site}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

Then I have another formula referencing the same external sheet, but one less column, which is returning an #INCORRECT ARGUMENT SET.

=SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

I've tried playing around with brackets, SUM, SUMIF, etc. but nothing works except if I put SUM, it only calculates the first column. What am I missing?

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Michelle Maas

    A SUMIFS Function requires a {Range} to be listed, then the "Criteria" that you're looking for in that range.

    Ex:

    =SUMIFS({Column to Sum}, {First Column}, "First Criteria", {Second Column}, "Second Criteria")

    What is it you're looking to do with the additional ranges added in? Are you looking to SUM the two different columns, but using the same criteria?

    If so, you'll want to actually add (+) two SUMIFS together, like so:

    =SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))


    Does that make sense? If this doesn't work, it would be helpful to see screen captures, but please block out sensitive data.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Michelle Maas

    No problem!

    For your other formula, it's missing a closing bracket after the second SUMIFS. You'll need to close off each statement fully before adding the next, like so:

    =SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula)

    Try:

    =SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

    And then you can add the fourth one on:

    =SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))


    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Michelle Maas

    A SUMIFS Function requires a {Range} to be listed, then the "Criteria" that you're looking for in that range.

    Ex:

    =SUMIFS({Column to Sum}, {First Column}, "First Criteria", {Second Column}, "Second Criteria")

    What is it you're looking to do with the additional ranges added in? Are you looking to SUM the two different columns, but using the same criteria?

    If so, you'll want to actually add (+) two SUMIFS together, like so:

    =SUMIFS({Name 2022 - OOH 1.5x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({Name 2022 - OOH 2x}, {Name 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))


    Does that make sense? If this doesn't work, it would be helpful to see screen captures, but please block out sensitive data.

    Cheers,

    Genevieve

  • Michelle Maas
    Michelle Maas ✭✭✭✭

    @Genevieve P. That worked fantastic!

    Now I have a similar one to add 3 columns, which is currently saying #NESTED CRITERIA, and I also want to include the formula below so it is one instruction (if possible):

    =SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)))

    =SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Michelle Maas

    No problem!

    For your other formula, it's missing a closing bracket after the second SUMIFS. You'll need to close off each statement fully before adding the next, like so:

    =SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula) + SUMIFS(formula)

    Try:

    =SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))

    And then you can add the fourth one on:

    =SUMIFS({NAME 2022 - Travel To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Site To Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Travel From Site}, {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022)) + SUMIFS({NAME 2022 - Total Hours}, {NAME 2022 - Area}, OR(@cell = "Travel"), {NAME 2022 - Date}, AND(IFERROR(MONTH(@cell), 0) = 2, IFERROR(YEAR(@cell), 0) = 2022))


    Cheers,

    Genevieve

  • Michelle Maas
    Michelle Maas ✭✭✭✭

    Awesome, thank you so much for your help. You saved me hours trying to figure it out!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad that this worked for you 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!