SUMIFS Summary Formula Issue

Options

Good afternoon -

I am trying to create a summary formula that sums the values in the range from Shared Inboxes to Metrics if the Date column is between 11/1/23 and 11/30/23.

I've created the following formula; however, it's returning #INCORRECT ARGUMENT SET.

Any suggestions? Thank you!!

=SUMIFS([Shared Inboxes]:Metrics, Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30))



Best Answer

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    Answer ✓
    Options

    I would suggest the following things:

    Add another column to sum the range for the rows. So use this as the column formula in the new helper column:

    =SUM([Shared Inboxes]@row:Metrics@row)

    Suppose this helper column is named as "Summation"

    Then use the following formula as your summary formula where it is needed:

    =SUMIFS(Summation:Summation, Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30))

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    @Justin Tackitt

    I had something similar I could pull from and used one like this before.

    =IF(AND([Date]@row >= DATE(2023, 11, 1), [Date]@row <= DATE(2023, 11, 30)), SUM([Shared Inboxes]@row:Metrics@row))

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/30/23
    Options

    Hi Justin

    I don't believe you can include multiple columns as the range for your SUMIFS

    This part in bold

    =SUMIFS([Shared Inboxes]:Metrics, Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30))


    You may need to create a separate SUMIF for each column and add them together.

    =SUMIFS([Shared Inboxes]:[Shared Inboxes], Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30)) +

    SUMIFS([Faxes]:[Faxes], Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30)) + ...


    Or add a helper column that holds the totals of all those columns (SUM) and then use that as the range in your SUMIFS.

    Helper column formula

    =SUM([Shared Inboxes]@row:Metrics@row)

    Revised SUMIFS formula

    =SUMIFS(Helper:Helper, Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30))


    Or, as you only have the two criteria, use an IF AND function.

  • Justin Tackitt
    Justin Tackitt ✭✭✭✭
    Options

    Ahh! I was wondering if that was it. Thank you for the help. Very much appreciated!

  • Shimanta Roy
    Shimanta Roy ✭✭✭✭
    Answer ✓
    Options

    I would suggest the following things:

    Add another column to sum the range for the rows. So use this as the column formula in the new helper column:

    =SUM([Shared Inboxes]@row:Metrics@row)

    Suppose this helper column is named as "Summation"

    Then use the following formula as your summary formula where it is needed:

    =SUMIFS(Summation:Summation, Date:Date, >=DATE(2023, 11, 1), Date:Date, <=DATE(2023, 11, 30))

  • Justin Tackitt
    Justin Tackitt ✭✭✭✭
    Options

    Thank you both!! Very much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!