Counting Values from Multiple Options, with @row

SkiPatrolScott

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...

Genevieve P.

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

Genevieve P.

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