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.

Registration roster with eight records, with criteria in F2 and G2 on the right and H2 ready for formula
Registration records are on the left, with space for criteria and the count result on the right.

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.

Formula bar shows =COUNTIFS(B2:B9,F2,C2:C9,G2) with H2 selected, displaying result 2 on the right
Check both ranges and criteria in the formula bar for H2.

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.

F2 is AM and G2 is Confirmed, with H2 displaying 1
After changing only the shift condition, the formula recalculates based on the new criteria.

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.

Reopened manual workbook showing F2 as AM, G2 as Waitlist, with H2 displaying 0 and retaining formula
After reopening, the worksheet retains the result where no records match.

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.

Cue AI panel, original registration records, PM and Confirmed criteria, and empty H2
Once Cue AI is open, verify the two criteria cells and that H2 is blank.

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.

Registration sheet saved and reopened in the same session, showing PM, Confirmed, count 2, and COUNTIFS in H2
After saving and reopening, H2 remains 2, corresponding to R03 and R05.

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.

Same reopened sheet with F2 changed to AM and G2 changed to Waitlist, showing H2 as 0
When changed to AM and Waitlist in the reopened copy, no single registration meets both conditions.

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.

Download Cue Office Free