How can I get a SUMIF function to return values for a month for several columns?

Crystal L
Crystal L
edited 12/20/21 in Formulas and Functions

=SUMIF(Date:Date,IFERROR(Month(@cell,0)=12),[Hope List]:[Trust List]))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    After correcting the syntax issue, you would need to essentially write out 8 SUMIF functions and add them all together.

    =SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Hope List]:[Hope List]) + SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Second Column]:[Second Column]) + SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Third Column]:[Third Column]) + ....................................

Answers

  • Hi @Crystal L, without asking any further questions, I assume that your trying to Sum everything out of the HopeList and TrustList Columns as a combined sum.

    It maybe a quick and easy way to run separate helper columns for both and a third as the total combined.

    Thanks

  • Thanks Graham. No, I was trying to sum all columns between and including the HopeList and TrustList columns. There are a total of 8 columns that need to be totaled based on the month criteria.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    After correcting the syntax issue, you would need to essentially write out 8 SUMIF functions and add them all together.

    =SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Hope List]:[Hope List]) + SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Second Column]:[Second Column]) + SUMIF(Date:Date, IFERROR(Month(@cell), 0) = 12, [Third Column]:[Third Column]) + ....................................

  • I think this solution provides a temporary fix for this limited data, yet if I had more than 8 columns this might propose an issue. Interestingly, the =SUM function works without the month criteria of course when selecting the beginning column of data and the ending column of data. Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. Using the SUM function on its own will work.

    The challenge with the date comparison is that all ranges within a function must be of the same size and shape.

    So if you have one date column that you are referencing, then you can only reference one column to be summed. If you reference 8 columns to be summed, then you would also need to reference 8 date columns so that both ranges within the SUMIF are the same size/shape (8 columns/all rows).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The other option would be to insert a text/number column and use a formula to sum all 8 columns together on each row.

    =SUM([1st Column]@row:[Last Column]@row)


    Then you can use the SUMIF to reference the single date column and this new single "total" column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!