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 :)
Answers

Good afternoon @emipathy,
Try adding IFERROR to your formula. Fixed? Column formula should work.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

@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?

@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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!