SumIfs - referencing to same Cell

Follow up
Follow up ✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter
  • Follow up
    Follow up ✭✭

    Hi @Kelly Moore ,

    Thank you for looking into this. Unfortunately it still say Invalid Data type. I have something like this:

    image.png


    I need to add all duration completion under the same meeting, due date and assigned to. Thank you for your help!

    https://community.smartsheet.com/discussion/comment/358137#Comment_358137


    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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?

    · Share on FacebookShare on Twitter
  • Follow up
    Follow up ✭✭

    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)

    image.png

    If I share a sample sheet are you able to help me? Thank you!

    · Share on FacebookShare on Twitter
  • Follow up
    Follow up ✭✭
    Meeting Point - Follow Up by.xlsx

    This is the excel

    · Share on FacebookShare on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!