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

Options

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.

Tags:

Best Answer

  • bourgeoj
    bourgeoj ✭✭✭
    Answer ✓
    Options

    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.

Answers

  • bourgeoj
    bourgeoj ✭✭✭
    Answer ✓
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!