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

bourgeoj

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.

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.

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.