SumIfs - referencing to same Cell

Follow up

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

Find more posts tagged with

Accepted answers

All comments

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

Follow up

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!

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

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?

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)

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

Follow up

Meeting Point - Follow Up by.xlsx

This is the excel

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.