Cross reference another sheet and filter the criteria

Dan K

Looking for a formula to pull data from another sheet and add 2 filter criteria.

Here is my filter critera but not sure how to reference the source smartsheet.

=COUNTIFS([Client Mitigation Status]:[Client Mitigation Status], "Pending") + COUNTIFS(SBG:SBG, "AERO")

Find more posts tagged with

Formulas

Accepted answers

All comments

Jgorsich

When you start entering a formula, after opening the parenthesis, there will be a popup that has a link to reference another smartsheet - from a mechanical point of view, you click that, then select the sheet you want, then select the portion of the sheet you want, then (if you want to prevent headaches in the future) you rename your selection and press enter.

Dan K

=COUNTIFS({SHEET REFERENCE}(SBG:SBG, "AERO" + {SHEET REFERENCE}(client mitigation status:client mitigation status, "pending"))

Something like this?

Jgorsich

@Dan K - close, but you are missing some commas there.

The tool tip when entering the formula should note that countifs works like this:

=Countifs(range1, test1, range2, test2, etc).

Those ranges are typically columns or portions of columns (I THINK you can do rows also, but I've never tried it) and have to cover the same number of rows - what it is ultimately counting is the number of rows where ALL of the conditions are met.

So, you've got this:

COUNTIFS({SHEET REFERENCE}(SBG:SBG, "AERO" + {SHEET REFERENCE}(client mitigation status:client mitigation status, "pending"))

I think what you are TRYING to do is check for rows where "Aero" appears in column SBG AND "pending" appears in your column "client mitigation status" in the second sheet. If that is the goal, your current formula is going to give you an error.

What you want to do is start typing "=countifs(". Once you do that, you should get the tool tip and the ability to reference another sheet. Click on that, find your other sheet, select your SBG column and (presuming your sheet is called SecondSheet) you'll see in the upper left portion of the window the name "SecondSheet Range1" - change that to "SecondSheet_SBG" and click okay. Now you'll have this: "=countifs({SecondSheet_SBG}". At this point you want to add a comm and your criteria and another comma: "=countifs({SecondSheet_SBG},"AERO",".

Now you want to AGAIN click the reference another sheet button and select your "client mitigation status" column and rename it to "SecondSheet_CMS" and hit okay. Then add your criteria for that as well and close your countifs - it should ultimately look like this: =countifs({SecondSheet_SBG},"AERO",{SecondSheet_CMS},"pending")

Then you should be good :)

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.