Need help with formula to COUNTM in a cross-sheet cell when Unique ID on both sheets MATCH

Hello. To enable dashboard reporting and visuals I am building a "Metrics" sheet that interprets data from a ginormous list (aka "G-List") -a separate sheet.

Both sheets have [Unique ID], and G-List has approx. 40 multiselect dropdown columns (one of which is [Strategy 2023-2027]).

I want my Metrics sheet to display each Unique ID's COUNTM value for [Strategy 2023-2027]. Basically to see how many strategic goals the item affects.

I'm not sure if I should use VLOOKUP, or INDEX/MATCH... or another IF statement and would really appreciate some direction.

Find more posts tagged with

Formulas

Accepted answers

bourgeoj

I think I figured it out...

=COUNTM(INDEX({G-List | Strategy 2023-2027}, MATCH([Unique ID]@row, {G-List | Unique ID}, 0)))

The result was 3 and the source G-List sheet did have 3 values selected.

All comments

bourgeoj

I think I figured it out...

=COUNTM(INDEX({G-List | Strategy 2023-2027}, MATCH([Unique ID]@row, {G-List | Unique ID}, 0)))

The result was 3 and the source G-List sheet did have 3 values selected.

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.