How to Count Rows That Match Two Conditions in Excel with COUNTIFS
Before assigning check-in staff, find out how many people have confirmed for each shift. Open your Excel registration workbook (.xlsx) in Cue Office and use COUNTIFS to count the rows where both the shift and the status match.
How Do You Match a Shift and Status on the Same Row?
Paste the practice data into cell A1 of a new worksheet. The left side contains registration records, with column B showing Shift and column C showing Status. Cells F2 and G2 on the right already contain the shift and status to count, leaving H2 for the formula.
Copy the formula below, select cell H2, and paste it in. B2:B9,F2 compares each record's shift against F2, while C2:C9,G2 compares the status on the same row against G2. Both criteria ranges span rows 2 through 9, matching row for row.
=COUNTIFS(B2:B9,F2,C2:C9,G2)
When F2 is PM and G2 is Confirmed, both R03 and R05 match, returning 2 in H2. R04 is PM, but its status is Pending. While R01 and R06 are both Confirmed, their shifts are AM and Eve respectively. Because each meets only one criterion, they are excluded from the count.
Why Does the Count Change to 1 or 0?
Change F2 from PM to AM while keeping G2 as Confirmed. Now only R01 satisfies both criteria simultaneously, so H2 updates to 1.
Next, change G2 to Waitlist. Although R07 is on the Waitlist, its shift is Eve. When AM and Waitlist appear only in separate records, H2 remains 0. Click Save As in the top-left toolbar, then close and reopen the copy to verify AM, Waitlist, the result, and the formula in H2.
How Can You Check That Cue AI’s Count Updates?
Prepare a copy preserving the initial criteria PM and Confirmed, and make sure H2 is blank. Open Cue AI from the Home tab.
Send the prompt below. Both criteria must be satisfied on the same row, and the result should only be written to H2.
How many registrations have both the shift in F2 and the status in G2 on the same row? Put the count in H2 and make it update when I change either of those two cells. Please leave the registration records, headings, and both condition cells unchanged.
Cue AI entered a COUNTIFS formula in H2 only. Save, close, and reopen the copy. Click H2 to check the formula. The original records and headings are still there, the conditions are still PM and Confirmed, and the count is still 2.
In the reopened copy, change F2 to AM and leave G2 as Confirmed. H2 updates to 1. Then change G2 to Waitlist. H2 updates to 0. You only need to edit the two condition cells to check that the count updates.
Practice with Registration Records
Show the full example
Paste the practice data into A1 of a new Cue Office worksheet. The data leaves columns D and E blank and H2 ready for the formula. Return to the counting steps.
Booking Shift Status Shift Status Matches R01 AM Confirmed PM Confirmed R02 AM Pending R03 PM Confirmed R04 PM Pending R05 PM Confirmed R06 Eve Confirmed R07 Eve Waitlist R08 AM Pending
Hand Off Confirmed and Waitlist Counts to Check-In Staff
When you prepare check-in lists for the next event, tell Cue AI which columns contain shifts and statuses, which cells hold the two conditions, and where the count should go. Compare the result with a count you have checked by hand, then switch the status to Waitlist. In this example, AM and Waitlist appear on different rows, so the morning team has no waitlist registrations to plan for.
Open the venue’s entry instructions (.pdf) in Cue Office for free and check the entry times for each shift against your Excel registration workbook (.xlsx). Use a Word handoff sheet (.docx) to record the confirmed and waitlist counts and who will contact people on the waitlist. Put each check-in desk’s shift schedule in a PowerPoint briefing (.pptx). Cue Office offers free spreadsheet, document, and presentation editing.