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
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!