SUMIFS referencing another sheet

Hello, I'm trying to use SUMIFS formulas to create a summary grid based on another grid.

=SUMIFS({Counter}, {Submission Date}, @cell = "", {Submission Status}, @cell <> "Waived", {Submission Status}, @cell <> "N/A", {Due in the next}, @cell = "1 Week", {TAT}, @cell = 3)

The formula should sum the numbers in the Counter column if the report has a blank submission date, submission status is not one of Waived or N/A, due in the next equals 1 Week, and turn around time equals 3.

I've tried both splitting Submission Status into separate arguments and combining with AND, as well as putting quotes around the "3" but neither makes a difference. All attempts result in "0".

What am I missing? Thanks for looking!

Find more posts tagged with

Accepted answers

DKazatsky

In my working version, I have a blank and it just wasn't counted. The rest continues to work as expected.

All comments

Dana G

@Paul Newcome @DKazatsky I figured it out! Smartsheet was having an issue with the operators.

=SUMIFS({CCO Contracted Deliverables - DATABASE Range 1}, {CCO Contracted Deliverables - DATABASE Range 6}, "1 Week", {CCO Contracted Deliverables - DATABASE Range 3}, "", {CCO Contracted Deliverables - DATABASE Range 4}, "3", {CCO Contracted Deliverables - DATABASE Range 8}, "")

Since I could not use <> for a couple expressions, I created a separate column that returned "No" if either were true, then told Smartsheet to look for blanks in that column.

Thanks for all your help (again!)

DKazatsky

@Dana G

I'm glad you have it working now. I'm still perplexed as to why your original formula works for me but not you.

Paul Newcome

@Dana G The COUNTIFS was not going to be the final solution. I was just using it for troubleshooting to try to narrow down where exactly the issue was (thus the reason for using the same range/criteria sets as opposed to a filter).

I'm glad you have it working now though.

Dana G

@Paul Newcome Gotcha! Even knowing how you troubleshoot is helpful for learning and trying to figure these out on my own. Have a great weekend and talk with you next time I get stuck :)

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.