Column Formulas in SUMIFS

Hi there,

I'm currently using a SUMIFS formula that is as follows:

=SUMIFS({v4 Hours}, {v4 end}, MONTH(@cell) = 12, {v4 Group}, OR(@cell = "AD", @cell = "Freelancer"))

I want to sum the task hours column {v4 Hours} if the End Date for the task is in December, and the group has one of 2 values. Each {reference} refers to a column in the referenced sheet. This formula works wonderfully.

The problem arises when the {task hours} column has a Column Formula in it. The column formula is: =(Lessons@row * [Hour/Lesson]@row) + ([Unit Count]@row * [Hours/Unit]@row).

Below you'll find the version of the formula I'm using. I'm only looking the Freelancer's hours in this case.

=SUMIFS({v6 Task hours}, {v6 End}, MONTH(@cell) = 12, {v6 Group}, "Freelancer")

{v6 Task Hours} is referencing the entire column that contains the Column Formula. Nothing I have done so far has been able to repair this. I even did a "tester" column where I copied the values into a new column and referenced that (using a new reference) and it worked just fine.

I want to know WHY the Column Formula is unable to be used in the SUMIFS. Also, if possible, I want to know a workaround that allows the Column Formula column to be used. I'm trying to roll this out across a larger team and have to have all my ducks in a row :)


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Good afternoon @emipathy,

    Try adding IFERROR to your formula. Fixed? Column formula should work.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @emipathy
    @emipathy ✭✭✭

    @Mark Cronk - Hi Mark,

    That did work! This formula enables it:

    =SUMIFS({v6 Task hours}, {v6 End}, IFERROR(MONTH(@cell), 0) = 12, {v6 Group}, "Freelancer")

    Is there an answer to WHY this is the solution?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭


    Date functions often cause errors if a cell is blank. 1 error causes the entire range to fail. Adding the IFERROR statement allows it to go past the error. You told the system to replace the error with a 0 and move on.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!