Skip across the chapter
Quota Playbook

3 COUNTIF counts to track each rep's Duo Mobile setup

Quota Playbook Editorial Team · Published · 12 min read

Track Duo Mobile setup with three COUNTIF counts: reps set up, reps needing re-activation, and reps on an OS too old for the app.

Key takeaways

  • Keep one row for each rep, and use only four fixed status labels in column D.
  • Check the Re-activate count first each week, since that rep is most likely stuck.
  • Compare each phone's OS version with the Duo Mobile version 4 minimums: iOS 17 on Apple devices and Android 12 on Android devices.
  • Write a short note whenever you change a status.

Why a shared sheet needs counts

When you onboard a new rep at a small B2B software company, the checklist grows fast. There is a laptop, a CRM login, an email account, a calendar, and then a security step that asks for a code or an approval from a phone. If you manage a small sales team, you probably track these tasks in a shared spreadsheet already.

The trouble is rarely the list itself. The trouble is that nobody looks at the Duo Mobile column until a rep cannot log in on their first day.

Your sheet does not need to explain how Duo works on the inside. It only needs to show, for each rep, whether Duo Mobile is working on their phone and whether anything needs attention this week. Three counts can do that job.

Microsoft describes COUNTIF this way: "Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion" ( Microsoft Support). That single idea covers most of what a sales manager needs. Pick a column, pick a status, and the sheet tells you how many reps match. You do not need pivot tables, macros, or a new tool.

Each of the three counts answers a question you will ask every week:

  1. How many reps have finished Duo Mobile setup?
  2. How many reps are waiting on a new activation?
  3. How many reps are using a phone that is too old for the app?

The same layout works for all three, so you can copy it and change the labels to fit your team.

Set up the columns first

Start with one row for each rep. Keep the layout flat, with no merged cells and no blank rows in the middle of the list. COUNTIF works on ranges, and a clean range is easier to trust. When a formula gives a surprising answer, a messy layout is usually the cause.

Use five columns:

  • A: Rep name
  • B: Phone type (iOS or Android)
  • C: OS version (for example, iOS 17, or the Android version shown in the phone's settings)
  • D: Duo Mobile status (a short label from a fixed list)
  • E: Notes (free text for anything unusual)

The status column matters most. If one rep is marked "Active," another is marked "active," and a third is marked "Done," your counts will drift from reality. Choose four labels, write them at the top of the sheet, and use only those. Most spreadsheet programs can show a dropdown in a cell, so people pick from the list instead of typing their own version.

Here are the four labels I suggest:

  • Active: Duo Mobile is installed and set up for this rep.
  • Pending: the rep has not finished setup yet.
  • Re-activate: the rep has a new phone, or the app needs a new activation.
  • Blocked: setup is stuck, and someone needs to step in.

These labels are a suggestion, not Duo terms. Match them to whatever your team already uses, as long as the list stays short and fixed.

A stray space after a word or a small typo will also break a match, and the count will look too low with no visible error. When a number seems off, check the spelling in the cells before you check the formula.

The examples below assume your rows run from row 2 to row 40. Extend each range as your team grows so it covers every new row. A range that stops early will quietly ignore a rep added below it, and that error is easy to miss.

Count 1: reps who are set up

The first count is the most basic one. It tells you how many reps have finished setup, which you can compare against the number of new hires you expected this month.

Enter this in a summary cell above the list:


=COUNTIF(D2:D40,"Active")


The formula checks cells D2 through D40 and counts each cell that says "Active." Microsoft's examples use the same pattern. One of them counts cells that match a value in a range, described as "the number of cells with peaches (the value in A4) in cells A2 through A5" ( Microsoft Support). Yours points at a different column and uses a different word.

A small improvement is to type the status word in its own cell and have the formula read that cell. If you type "Active" in cell G3, the formula reads:


=COUNTIF(D2:D40,G3)


Now you can change the label in one place, and every count that depends on it updates.

Compare the result with your target. Suppose you onboarded six reps this month and the count shows four are Active. That is not a failure. It is a short list of two names to check. Filter column D for anything other than Active, then read the notes on those rows. You will usually find one of three things: the rep has not started, the rep is waiting on someone, or there is a problem you can fix in a few minutes.

Keep in mind that the count only reflects what someone has entered. If a rep finished setup but nobody updated the cell, the count will look behind. That is a prompt to ask, not a reason to panic.

Count 2: reps who need to re-activate

The second count prevents the most lost time. Duo's common issues guide states: "If you get a new phone you'll need to re-activate Duo Mobile" ( Cisco Duo). A rep who replaces a phone will hit this step. They may not mention it, and you may only learn about it when they message you on a busy day. The count lets you catch it first. For a rep locked out after losing a phone, see lost seller phone recovery.


=COUNTIF(D2:D40,"Re-activate")


If the answer is above zero, filter column D for the label, and you will see exactly which reps are affected.

Next, decide who will do the re-activation. The same guide describes two paths. The first is self-service: "You may enroll your new device yourself using Duo's device management portal if self-service is enabled" ( Cisco Duo). If self-service is not available, the guide says to "ask your administrator to send you a new activation link" ( Cisco Duo).

Record which path each rep is on in the Notes column. A short phrase such as "self-service" or "asked admin, waiting" is enough. Add the date the rep asked, so you can see who has waited longest. Pick your own rule for how long is too long, write it down, and apply it to everyone the same way.

A rep does not need to feel bad about a new phone. It is a normal part of working life, and a clear note makes the next step simple for everyone involved.

Find out who holds the administrator role for your Duo account during onboarding, and note that name in the header area. When a rep is stuck, you will know who to ask without digging through old email. For a backup plan, see backup admin checklist for sales tool logins.

Count 3: reps on a phone that is too old

The third count is about phone software, and it is the one most managers skip. A status label shows only what someone last wrote down. It does not show the software version on the phone right now. The second count catches reps who need a new activation. The third catches reps whose phone software sits below the minimum for the app.

Duo's common issues page gives minimum operating systems for Duo Mobile version 4. For Apple devices, it says: "The minimum supported operating system version for Duo Mobile version 4 on Apple devices is iOS 17." For Android devices, it says: "The minimum supported operating system version for Duo Mobile version 4 on Android devices is Android 12" ( Cisco Duo). Because these minimums apply to version 4, confirm which version your reps install, and check the page again when you set up the sheet. If the minimums change, update your formula to match.

For this count, column C has to hold a version you can match. Suppose a rep's iOS phone runs a version older than iOS 17, and another rep's Android phone runs a version older than Android 12. Write the exact older-version text your sheet uses, then write one COUNTIF for each:


=COUNTIF(C2:C40,"iOS <older version>")+COUNTIF(C2:C40,"Android <older version>")


Add a COUNTIF for every older version that could appear on your team. A formula that lists only one old version will miss the others, so review the OS column once a month and add any new old version you find.

This pattern follows Microsoft's apples-and-oranges example, which counts two values in one range: "Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5" ( Microsoft Support). Each COUNTIF does one job, and the plus sign combines the results.

The third count tells you a phone is below the minimum. It does not fix the phone. The usual next step is a conversation. The rep updates the phone software if the device allows it, or the company provides a newer phone. Write the outcome in the Notes column so the next reader knows what was decided.

Turn the counts into a weekly check

Place the three summary formulas in one small block, each with a label beside it: Set up, Re-activate, and Old OS. Keep that block where you will see it first each time you open the file.

Then set a standing time each week to review them. A short session is enough for a small team. Work through the counts in this order:

  1. Start with Re-activate. A rep waiting on a new activation is the one most likely to be stuck right now.
  2. Move to Old OS. These reps may be fine today, but a phone update or device swap is easier to plan before a problem appears.
  3. Finish with Set up. This count shows progress for new hires, and it is the number you can share with leadership when they ask how onboarding is going.

Keep the order fixed. If you always start with the progress number, urgent items can sit for a week without anyone noticing.

If a teammate helps with onboarding, share the file and walk them through the four labels once. Someone who understands the statuses and the three counts can take over the weekly check without a long handover.

Common mistakes to avoid

Most problems with this kind of sheet come from a few habits. Watch for these.

Mixed labels. If "Re-activate" and "Reactivate" both appear in column D, the second count misses one of them. Stick to the labels you wrote down.

Ranges that stop too early. When you add a rep, check that every formula still covers their row. Count the names in column A and check that the same number of rows carries one of the four status labels. If they do not match, a range is probably too short.

Trusting the status over the OS. A status can go stale. A rep may be marked Active, and their phone may have been replaced since. The third count reads the OS column directly, so use both.

Dropping the notes. The counts show how many reps need attention. The notes show why. Write a short phrase every time you change a status.

Treating the sheet as the system of record. Your spreadsheet is a manager's view of onboarding. If the sheet and the Duo device management portal disagree, trust the portal, then fix the sheet.

A sample week with the three counts

Here is what the sheet might look like for a team of six new reps, with every row filled in. Your details will differ, but the pattern holds.

Rep Phone OS version Status Notes
rep one iOS iOS 17 or newer Active Set up on the first day
rep two Android Below Android 12 Pending Phone update needed
rep three iOS Below iOS 17 Re-activate New phone; asked admin earlier this week
rep four Android Android 12 or newer Active No issues noted
rep five iOS iOS 17 or newer Blocked Waiting on activation link
rep six Android Android 12 or newer Pending Starts next week

At the start of the week, the summary block shows two for Set up, one for Re-activate, and two for Old OS. The Old OS count includes the second and third reps, since their phones are below the minimums on the Duo page. That result sets the order for the week. The third rep needs the administrator to confirm where the request stands. The second rep needs a phone update or a device conversation. The fifth rep is blocked, so you check the notes and see that the activation link is still outstanding.

The counts did not say why the fifth rep is blocked, or whether the second rep's phone can be updated. They pointed you to the right rows. The notes and a short conversation do the rest. The formulas find the rows, and you handle the people.

Illustrative example: 10 reps in one month

Suppose a team of 10 reps starts in one month, and the sheet shows 5 Active, 3 Pending, 1 Re-activate, and 1 Blocked. Those four totals add up to 10. Two reps have an OS below the Duo minimums, so the Old OS formula returns 2.

Those numbers are illustrative only. Your team will have its own totals.

Start with the formulas today

You do not need to rebuild your sheet to use this approach. Add the five columns if they are missing, clean up the labels in column D, and build a summary block from the formulas above. Then run the weekly check once. For a wider check of devices before reps call, see device checks before reps call. The first run often surfaces one or two reps who need attention right away, and that alone makes the effort worthwhile.

Once the habit is in place, the sheet is easy to keep up. Each new rep gets a row, each status change gets a note, and each week you read three numbers. That keeps onboarding on track and can stop a rep from losing access on a day when you could have prevented it.

Frequently asked questions

Do I need a new tool to track Duo Mobile setup?

No. You do not need pivot tables, macros, or a new tool, because COUNTIF counts the cells that meet a criterion ( Microsoft Support).

What should I do when a rep gets a new phone?

A rep with a new phone must re-activate Duo Mobile. Duo's guide says they may enroll the new device themselves in the device management portal if self-service is enabled, or ask their administrator for a new activation link ( Cisco Duo).

What is the minimum OS for Duo Mobile version 4?

The minimum is iOS 17 on Apple devices and Android 12 on Android devices ( Cisco Duo).

Sources

  1. Driver's license checks: 3 steps for seller job posts
  2. Device and 2FA checks: 4 fixes before reps call
  3. New-rep training rollout figures: 4 to read first

More plays