Column Formulas in SUMIFS

@emipathy

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

Good afternoon @emipathy,

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

Mark

@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

@emipathy,

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.

Mark