SumIfs - referencing to same Cell
Hi Team, I have to calculate a number based on 3 criteria, i tried this formula but it says Invalid:
=SUMIFS([Duration Completed]:[Duration Completed],
Meeting:Meeting, Meeting@row,
[Start Date]:[Start Date], [Start Date]@row),
[Assigned To]:[Assigned To], [Assigned To]@row))
I tried Has and Contains but still showing invalid
Answers
-
Hey @Follow up
There are a couple of misplaced parentheses in your formula.
Try this
=SUMIFS([Duration Completed]:[Duration Completed], Meeting:Meeting, Meeting@row, [Start Date]:[Start Date], [Start Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)
Does this work for you?
Kelly
-
Hi @Kelly Moore ,
Thank you for looking into this. Unfortunately it still say Invalid Data type. I have something like this:
I need to add all duration completion under the same meeting, due date and assigned to. Thank you for your help!
-
Hey @Follow up
Does your data set contain any errors in your duration field? Is your Meeting Calc column a Text/Number field? Let's try a couple of filters to see if they help
=SUMIFS([Duration Completed]:[Duration Completed], [Duration Completed]:[Duration Completed], ISNUMBER(@cell), [Start Date]:[Start Date], ISDATE(@cell), Meeting:Meeting, Meeting@row, [Start Date]:[Start Date], [Start Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)
Are you still getting an error?
-
Hi @Kelly Moore,
It still doesn't work. I tried this to excel and the formula i used works without any error. and its the same as this:
=SUMIFS([Duration Completed]:[Duration Completed], Meeting:Meeting, Meeting@row, [Start Date]:[Start Date], [Start Date]@row,[Assigned To]:[Assigned To], [Assigned To]@row)
If I share a sample sheet are you able to help me? Thank you!
-
This is the excel
-
Hey @Follow up
Looking at your excel file, it appears you have errors in your data set. For the [Duration Completed] column formula, I would like you to wrap that formula in an IFERROR function. This will clean up your data and allow the SUMIFS to run. You could do a test to prove those existing errors are a problem - try this SUM([Duration Completed]:[Duration Completed]). I believe you will receive the same error as with the SUMIFS. In case you haven't used IFERROR before, you will encompass the entire existing formula, parentheses and all, within an IFERROR. You must designate what you want the IFERROR response to be. In this case I suggest either a blank, or a zero. I'll put in a blank.
=IFERROR(your entire existing formula with all parentheses, "")
If desired, You could also apply an IFERROR to your [Duration Passed] and [Column15] formulas as well.
Does the IFERROR in your [Duration Completed] column clear your SUMIFS error? Don't apply an IFERROR to your SUMIFS function - we need to make sure it is working properly before you mask any errors there.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!