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!