SUMIFS Issue

Options

I'm having a hard time evolving the following SUMIF formula into a SUMIFS formula so I can account for the month and the year. Here is my SUMIF formula:

=SUMIF(MONTH([Billing 1 Date]@row), =3, [Billing 1]@row) + SUMIF(MONTH([Billing 2 Date]@row), =3, [Billing 2]@row) + SUMIF(MONTH([Billing 3 Date]@row), =3, [Billing 3]@row) + SUMIF(MONTH([Final Billing Date]@row), =3, [Final Billing]@row)

Please help!

Thank you!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @krollshow

    My mistake! I left a bunch of commas in there that are screwing it up. You'd need those commas for SUMIFS or COUNTIFS, but they will break IF functions! Here is where I left the errant commas in each IF:


    Fixed:

    =IF(AND(MONTH([Billing 1 Date]@row) = 3, YEAR([Billing 1 Date]@row) = 2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row) = 3, YEAR([Billing 2 Date]@row) = 2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row) = 3, YEAR([Billing 3 Date]@row) = 2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row) = 3, YEAR([Final Billing Date]@row) = 2022), [Final Billing]@row, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    The syntax is a bit different with SUMIFS. With SUMIFS, you list the range that you want to add, followed by criterion range 1, criterion 1, criterion range 2, criterion 2, etc.

    It looks like you may not need that though. In fact, you might not need SUMIF or SUMIFS for what you are doing. Let me see if I can translate your formula into English:

    If the month value in [Billing 1 Date]@row is 3, add the value in Billing 1 on this row... to what? SUMIF and SUMIFS usually apply to a range, not a single cell.

    It looks like you really just need a string of four IF/AND statements that get added together. Each one says "IF these two conditions are true, give me the value in this cell, otherwise give me 0". So in the end, you're adding 4 values together to get a total.

    =IF(AND(MONTH([Billing 1 Date]@row), =3, YEAR([Billing 1 Date]@row), =2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row), =3, YEAR([Billing 2 Date]@row), =2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row), =3, YEAR([Billing 3 Date]@row), =2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row), =3, YEAR([Final Billing Date]@row), =2022), [Final Billing]@row, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • krollshow
    Options

    Than you, Jeff! For some reason i'm still getting an invalid data type error?


  • krollshow
    Options

    Also, given the context of what I'm trying to accomplish maybe I'm going about it in a obscure way. I'm attempting total up a number of project billings that land in the same month. So that I can build a proper spending curve. Like this:


    This is my data set:


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @krollshow

    My mistake! I left a bunch of commas in there that are screwing it up. You'd need those commas for SUMIFS or COUNTIFS, but they will break IF functions! Here is where I left the errant commas in each IF:


    Fixed:

    =IF(AND(MONTH([Billing 1 Date]@row) = 3, YEAR([Billing 1 Date]@row) = 2022), [Billing 1]@row, 0) + IF(AND(MONTH([Billing 2 Date]@row) = 3, YEAR([Billing 2 Date]@row) = 2022), [Billing 2]@row, 0) + IF(AND(MONTH([Billing 3 Date]@row) = 3, YEAR([Billing 3 Date]@row) = 2022), [Billing 3]@row, 0) + IF(AND(MONTH([Final Billing Date]@row) = 3, YEAR([Final Billing Date]@row) = 2022), [Final Billing]@row, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • krollshow
    Options

    AWESOME, thank you so much, Jeff! This worked perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!