Cellwise Start free

Excel guide · 6 min read

How to calculate ticket reopen rate in Excel

Quick answerReopen rate = tickets reopened / tickets solved in the same period. Count each reopened ticket once, not each reopen event: =COUNTA(UNIQUE(I2:I7))/COUNTA(A2:A20) in Excel 365. Most teams also only count reopens within a window (for example 7 days after solving), which a helper column with reopen date - solved date handles. On the sample, 4 of 19 October tickets reopened within 7 days: 21.1%.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1TicketAgentCategorySolvedFirst contactFirst reopenDaysReopened in windowReopen ticketReopen dateWindow (days)7
2T-1001AnaBillingOct 1, 2026YesNoT-1003Oct 5, 2026Min tickets5
3T-1002BenLoginOct 2, 2026YesNoT-1004Oct 7, 2026Report dateNov 3, 2026
4T-1003AnaShippingOct 2, 2026No463003YesT-1004Oct 14, 2026
5T-1004ChloeBillingOct 5, 2026Yes463022YesT-1007Oct 21, 2026
6T-1005BenBillingOct 6, 2026YesNoT-1014Oct 27, 2026
7T-1006AnaLoginOct 7, 2026YesNoT-1018Nov 2, 2026
8T-1007BenShippingOct 9, 2026No4631612No
9T-1008AnaBillingOct 12, 2026YesNo
10T-1009ChloeLoginOct 13, 2026NoNo
11T-1010BenLoginOct 14, 2026YesNo
12T-1011AnaShippingOct 16, 2026YesNo
13T-1012BenBillingOct 19, 2026NoNo
14T-1013ChloeShippingOct 20, 2026YesNo
15T-1014AnaLoginOct 22, 2026Yes463225Yes
16T-1015BenShippingOct 23, 2026YesNo
17T-1016AnaBillingOct 26, 2026NoNo
18T-1017BenLoginOct 28, 2026YesNo
19T-1018ChloeBillingOct 29, 2026Yes463284Yes
20T-1019AnaLoginOct 30, 2026YesNo

Download this sheet as an Excel practice file (.xlsx)

Set up the solved log and the reopen log

Help desk exports usually give you two lists. The sample has 19 tickets solved in October 2026 (A:E: ticket, agent, category, solved date, and whether it was solved on first contact) and a separate reopen log (I:J) with one row per reopen event. T-1004 reopened twice, so it appears twice in the log.

Put the settings in input cells: the reopen window in days in L1 (7), the minimum number of tickets before you report a rate in L2 (5), and the report date in L3 (3 November 2026). Use a fixed report date rather than TODAY(), so the October report shows the same numbers when someone opens it next month.

The first number most people calculate is reopen events / solved tickets. That's 6 / 19 = 31.6%, and it's too high: T-1004 is counted twice. A ticket that reopens three times is one unhappy customer, not three solved tickets that failed.

=COUNTA(I2:I7)/COUNTA(A2:A20)

Result: 0.31578947368 shows as 31.6% with Percent format

0.316 or 31.6%: reopen events divided by solved tickets, which double counts T-1004.

Count each reopened ticket once

In Excel 365 and 2021, UNIQUE removes the repeat IDs before counting: =COUNTA(UNIQUE(I2:I7)) gives 5 reopened tickets, so the rate is 5 / 19 = 26.3%. In older versions, count it from the solved log instead: =SUMPRODUCT(--(COUNTIF(I2:I7,A2:A20)>0)) counts the solved tickets that appear in the reopen log at least once.

Even better, put the reopen data on each ticket's row. F2 finds the first reopen date with =IF(COUNTIF($I$2:$I$7,A2)=0,"",MINIFS($J$2:$J$7,$I$2:$I$7,A2)). MINIFS returns the earliest date for that ticket (Excel 2019 and later); the COUNTIF check leaves the cell blank for tickets that never reopened, because MINIFS returns 0 when nothing matches. Now each ticket is one row, and every rate below is a simple COUNTIFS.

Make sure both lists use the same ticket ID format. "T-1004" in one export and "1004" or "T-1004 " with a trailing space in the other won't match, and the ticket silently counts as never reopened. TRIM both columns if in doubt.

=COUNTA(UNIQUE(I2:I7))/COUNTA(A2:A20)

Result: 0.26315789474 shows as 26.3% with Percent format

0.263 or 26.3%: 5 different tickets reopened out of 19 solved.

Add a reopen window

A ticket that comes back three weeks later is often a new problem with the same customer, not a failed fix. Most teams count a reopen only within a set number of days after solving. Pick the window to match your own definition and keep it in L1.

G2 is the days between solving and the first reopen, =IF(F2="","",F2-D2), and H2 flags it: =IF(G2="","No",IF(G2<=$L$1,"Yes","No")). T-1007 reopened 12 days after it was solved, so with a 7-day window it doesn't count. Change L1 to 14 and it would.

The window also decides which tickets you can judge yet. A ticket solved on 30 October has only had 4 days to reopen by the 3 November report date. To report a final rate, only count tickets solved at least a full window before the report date: =COUNTIFS(H2:H20,"Yes",D2:D20,"<="&L3-L1)/COUNTIFS(D2:D20,"<="&L3-L1) covers the 16 tickets solved by 27 October and gives 3 / 16 = 18.8%. The month's figure firms up a week after month end.

=COUNTIF(H2:H20,"Yes")/COUNTA(A2:A20)

Result: 0.21052631579 shows as 21.1% with Percent format

0.211 or 21.1%: 4 of 19 tickets reopened within 7 days of being solved.

Reopen rate by agent and category, with a minimum-ticket warning

With the flag on every row, a rate per agent is reopened tickets / solved tickets for that agent. The Excel 365 formula below builds the whole table in one go: UNIQUE lists the agents, COUNTIF counts their solved tickets, COUNTIFS counts their reopened ones, and IF shows "Too few" instead of a rate when an agent has fewer tickets than L2. In older Excel, type the agent names and use =COUNTIFS(B2:B20,N2,H2:H20,"Yes")/COUNTIF(B2:B20,N2).

The warning matters. Chloe solved 4 tickets and 2 of them reopened, which is 50% on paper. One more good week would halve it. Small numbers swing wildly, so set a minimum and don't rank people on a handful of tickets. Ben shows 0%, but his T-1007 did come back (after 12 days), which is a reminder that the window choice changes who looks good.

By category, swap B for C: Billing 28.6% (2 of 7), Login 14.3% (1 of 7), Shipping 20% (1 of 5). A category with a high reopen rate across several agents usually points at a process or product problem (a refund that takes longer than the agent promised, say), not at one person.

=LET(a,UNIQUE(B2:B20),n,COUNTIF(B2:B20,a),r,COUNTIFS(B2:B20,a,H2:H20,"Yes")/n,HSTACK(a,n,IF(n<L2,"Too few",ROUND(r,3))))

Result: spills 3 rows × 3 columns

Ana80.25
Ben70
Chloe4Too few

Ana 8 tickets, 0.25; Ben 7 tickets, 0; Chloe 4 tickets, "Too few": under the minimum of 5, so no rate is shown.

How reopen rate relates to first contact resolution

First contact resolution asks whether the ticket was solved in one interaction. Reopen rate asks whether it stayed solved. They overlap less than people think: a ticket can be closed on the first contact and still reopen, because the agent closed it too soon.

In the sample, 14 of 19 tickets are marked as first-contact (73.7%), and 3 of those reopened within the window (T-1004, T-1014 and T-1018). A stricter FCR counts a ticket only if it was first-contact and didn't reopen: =COUNTIFS(E2:E20,"Yes",H2:H20,"No")/COUNTA(A2:A20) gives 57.9%. Report both numbers, or the stricter one, so closing tickets early doesn't make FCR look better.

Read reopen rate next to SLA compliance and customer effort too: a team that rushes to hit a resolution-time target often pays for it in reopens a week later.

=COUNTIFS(E2:E20,"Yes",H2:H20,"No")/COUNTA(A2:A20)

Result: 0.57894736842 shows as 57.9% with Percent format

0.579 or 57.9%: tickets solved on first contact that also stayed solved, against 73.7% when reopens are ignored.

Common mistakes

Counting reopen events instead of tickets

A ticket that reopens twice is one reopened ticket. Use UNIQUE on the IDs, or flag each ticket once with COUNTIF.

Mixing periods

Count reopens against the tickets solved in the period, even if the reopen happened next month (T-1018 was solved 29 October and reopened 2 November). Counting October's reopen events against October's solves mixes two groups of tickets.

Reporting before the window has closed

Tickets solved in the last days of the month haven't had time to reopen. Only count tickets solved at least a window before the report date, or mark the month as provisional.

Ranking agents on a few tickets

2 reopens out of 4 is 50%. Set a minimum ticket count in an input cell and show "Too few" below it.

FAQ

How do you calculate ticket reopen rate?

Divide the number of tickets that reopened by the number of tickets solved in the same period. Count each ticket once even if it reopened several times, and decide whether only reopens within a set number of days count.

What is a good ticket reopen rate?

It depends on your product, channel and how you define a reopen, so compare against your own history rather than a published figure. Watch the trend and the categories with the highest rates.

How do I count unique reopened tickets in Excel?

In Excel 365, =COUNTA(UNIQUE(range)) on the reopened ticket IDs. In older versions, =SUMPRODUCT(--(COUNTIF(reopen_ids,solved_ids)>0)) counts the solved tickets that appear in the reopen list at least once.

Should a ticket reopened after 30 days count?

That's a policy choice. Many teams use a window of a few days to two weeks and treat later contacts as new tickets. Keep the window in an input cell so you can report it either way.

What's the difference between reopen rate and FCR?

FCR is the share of tickets solved in one contact; reopen rate is the share that came back after being solved. A ticket can be both first-contact and reopened, so a strict FCR excludes reopened tickets.

Functions used

Related guides

Updated