Counting Values from Multiple Options, with @row

Options

I've been successful with much help from @Paul Newcome @Genevieve P. @Kelly Moore and others. Up against a deadline for a meeting with the Suits tomorrow, and of course I want to add one more carrot... I have a Route Qual Builder, we will be adding Routes as LEAD Q, Second Q,... this is feeding numerous sheets to identify various quals based on the patroller.

I also have a sheet for Routes, this is pulling from numerous sheets, counting routes, shots, ....

I have information about the Patroller, but what I want to display is how many Patrollers (not who) are Lead Qualified, Second Qualified....ive tried numerous count ifs but cant seem to figure it out.

So the L-Sum would pull from the Qual Builder Sheet, all the instances where a Lead Q was identified to a patroller. My hope is to give the Suits a planning tool to increase or decrease future planning. so the L-Sum may show-- 5 for Straw 1, 5 for Straw 2, 5 for Straw 3... but what it will really say is, STRAW 1 is an unfrequented route, and STRAW 3 is a priority, lets identify more patrollers to get Q on STRAW 3, prior to Straw 1...

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/02/23 Answer ✓
    Options

    Hi @SkiPatrolScott

    Working with multi-select columns can get complicated! 🙂

    Try using a HAS Function within your COUNTIF:

    =COUNTIF({Lead Q Column Reference}, HAS(@cell, [ROUTE QUAL]@row))

    This should tell you how many times the current row's Route Qual has been selected on a row in your first sheet. Then for the S-Sum you'd do the same thing, but reference the next Q column:

    =COUNTIF({Second Q Column Reference}, HAS(@cell, [ROUTE QUAL]@row))

    Let us know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/02/23 Answer ✓
    Options

    Hi @SkiPatrolScott

    Working with multi-select columns can get complicated! 🙂

    Try using a HAS Function within your COUNTIF:

    =COUNTIF({Lead Q Column Reference}, HAS(@cell, [ROUTE QUAL]@row))

    This should tell you how many times the current row's Route Qual has been selected on a row in your first sheet. Then for the S-Sum you'd do the same thing, but reference the next Q column:

    =COUNTIF({Second Q Column Reference}, HAS(@cell, [ROUTE QUAL]@row))

    Let us know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!