Filtering with a formula, but stuck

Hello, I'm trying to filter a summary sheet metric using the following formula but I keep getting "NOMATCH" or "UNPARSEABLE" depending on what I move around. The issue seems to be when I add the Country criteria. I want to filter to just United States and Canada:

=SUMIFS([Total employees]:[Total employees], [Country]:[Country], OR(CONTAINS("Canada", @cell),("United States", @cell)), [Department]:[Department], CONTAINS("Customer Fulfillment", @cell), Date:Date, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 3, 31)))

The Country column has a column formula applied to it:

=INDEX({Master Location Index - Country Range}, MATCH([Location]@row, {Master Location Index - Location Range}, 0), 0)

I've searched all over the community for examples similar to this and am not coming up with a solution.

Thanks so much for your help!

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Kelly Moore

Hey @curiouslearner

A very small tweak to your formula is all that is needed. Both of the terms in your Country OR criteria need the CONTAINS function.

=SUMIFS([Total employees]:[Total employees], [Country]:[Country], OR(CONTAINS("Canada", @cell), CONTAINS("United States", @cell)), [Department]:[Department], CONTAINS("Customer Fulfillment", @cell), Date:Date, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 3, 31)))

Will that work for you?

Kelly

All comments

Kelly Moore

Hey @curiouslearner

A very small tweak to your formula is all that is needed. Both of the terms in your Country OR criteria need the CONTAINS function.

Will that work for you?

Kelly

curiouslearner

Hi Kelly, thanks so much for your quick reply. I appreciate you!

This did not work initially, but combined with another thread I got it to work. I had to apply an "IFERROR" enclosure to the column formula for Country and combined with your fix above I have it working.

Thanks again.

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.