How to Count Cells That End With in Excel

Why This Task Matters in Excel

Imagine you manage a product catalog and every discontinued item code ends with “-DISC”. At month-end you must report how many discontinued products are still in stock. Or picture an HR analyst who stores employee IDs where temporary contractors end with “-T”. Quickly counting how many rows fit that suffix can save hours of manual filtering.

Suffix-driven counting appears in dozens of industries:

• Retail: SKUs that end with “-S” for seasonal, “-CLR” for Clearance
• Logistics: Tracking numbers ending in specific hub codes like “US” or “EU”
• Manufacturing: Batch numbers that finish with the year, e.g. “23”
• Finance: General-ledger accounts that end in “00” to mark control accounts
• Marketing: Campaign codes terminating with country abbreviations (-CA, ‑UK)
• Education: Course codes ending with “L” for lab sections

Whenever naming conventions embed meaning at the tail end of a string, counting by “ends with” is the fastest way to generate metrics, validate data quality, or feed dashboards.

Excel excels at this because it combines wildcards, text functions, and array formulas inside familiar SUM or COUNT wrappers. You can build a dynamic one-cell formula that updates automatically when the suffix list or data range grows—no programming required. Without this skill, analysts often waste time filtering manually, accidentally double-count, or overlook case-sensitivity nuances. Mastering suffix counts also lays the groundwork for more advanced tasks such as conditional formatting, dynamic aggregation with SUMIFS, and data cleansing with Power Query.

In short, being able to count cells that end with a particular substring is a foundational capability that keeps workbooks automated, auditable, and scalable.

Best Excel Approach

For most scenarios, the simplest and fastest method is the COUNTIF function with a wildcard that captures “any characters, then the suffix.” The asterisk (*) wildcard represents any length of text. Because COUNTIF is optimized inside Excel’s calculation engine, it performs well even on tens of thousands of rows.

Syntax overview:

=COUNTIF(range,"*" & suffix)

Parameters

• range – the cells you want to evaluate (e.g. [A2:A1000]).
• suffix – the exact characters that must appear at the end of each cell (e.g. \"-DISC\"). You can hard-code the text inside the criteria or reference another cell.

Why this method is usually best:

• One function, single criterion, minimal complexity
• Ignores numeric vs text types automatically (Excel coerces)
• Handles blanks elegantly (they simply do not match)
• Backward compatible with Excel 2007 and later

When to consider an alternative:

• You need case-sensitive matching (COUNTIF is case-insensitive)
• You must test multiple suffixes at once (COUNTIFS or SUMPRODUCT)
• You are working inside dynamic arrays and prefer FILTER / COUNTA for spill results
• Your suffix includes a literal question mark (?) or asterisk (*) that interferes with wildcards

An alternative, case-sensitive formula:

=SUMPRODUCT(--(RIGHT(range,LEN(suffix))=suffix))

Here RIGHT extracts the ending characters, LEN measures the suffix length, and the logical test yields a TRUE/FALSE array that SUMPRODUCT converts to a count.

Parameters and Inputs

To guarantee reliable results, pay attention to the following inputs:

1. Range (required)

• Must be a contiguous or non-contiguous range containing text or mixed data.
• Avoid entire columns (A:A) on very large sheets because each recalc will scan over one million rows.
• Named ranges and structured table references are fully supported.

2. Suffix (required)

• Text string, can be typed directly within double quotes or stored in a cell such as [D1].
• Must represent exactly what you expect at the end of the cell, including dashes, spaces, or leading zeros.
• If the suffix itself contains an asterisk or question mark you must precede those characters with a tilde () in COUNTIF criteria: \"​*\" & \"*\" & \"XYZ\"

3. Optional: Case sensitivity switch

• Not an argument per se, but choosing COUNTIF (case-insensitive) vs SUMPRODUCT+RIGHT (case-sensitive) is an input decision.

Data preparation

• Trim extra spaces with TRIM or Power Query—trailing spaces will break RIGHT matching.
• Ensure cells are stored as text if they may be interpreted as numbers; leading zeros can disappear otherwise.
• Remove or standardize hidden characters like line feeds.

Edge cases

• Empty cells never match the suffix.
• Cells shorter than the suffix length never match because RIGHT will return the entire cell, which will be unequal.
• COUNTIF treats logical TRUE/FALSE as text \"TRUE\"/\"FALSE\" during comparison.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose column [A] holds 50 product codes. You want to count how many end in “-DISC”.

Sample data in [A2:A11]:

P-100-DISC
P-101
P-102-DISC
P-103
P-104-DISC
P-105
P-106
P-107-DISC
(blank)
P-109-DISC

Step-by-step:

1. Select cell [B2] (your result cell).
2. Enter the formula:

=COUNTIF(A2:A11,"*" & "-DISC")

3. Press Enter. The cell returns 5, matching the five items with the “-DISC” suffix.

Why it works:

• The criteria \"\"&\"-DISC\" resolves to \"-DISC\". The asterisk allows any sequence (including zero characters) before the dash.
• COUNTIF loops through each cell, applies the wildcard pattern, and increments the count for every match.

Variations:

• Store \"-DISC\" in cell [D1] and use:

=COUNTIF(A2:A11,"*" & D1)

• Change the asterisk to “??” if you require exactly two characters before “-DISC”, but that exceeds the “ends with” use case, so wildcard star is normally preferred.

Troubleshooting:

• If you get zero when you expect positives, reveal hidden spaces with LEN(A2). Trim or clean the data as needed.
• If some entries are numbers and the suffix is numeric (e.g. “23”), ensure cells are formatted as text; otherwise 1023 and 1123 saved as numbers will still match because COUNTIF coerce them to text behind the scenes, but RIGHT approach may need TEXT conversion.

Example 2: Real-World Application

Scenario: A logistics manager tracks 3,500 tracking IDs in an Excel Table named TblPackages. IDs that end with “US” denote domestic shipments. The dashboard needs the domestic shipment count and percentage of total shipments.

Data setup:

• Table column [TrackingID] in rows 2-3501
• Result cells in a Summary sheet: [B3] for total packages, [B4] for domestic count, [B5] for domestic percentage

Steps:

1. Calculate total packages:

=ROWS(TblPackages[TrackingID])

2. Count domestic:

=COUNTIF(TblPackages[TrackingID],"*" & "US")

Because TblPackages is an Excel Table, the reference automatically expands when new rows are added.

3. Compute percentage:

=B4/B3

Format [B5] as Percent with one decimal place.

Business impact: Each time new packages are imported, the table grows; the formula recalculates instantly. No filter clicking, no manual recounting. Management always sees up-to-date domestic shipment statistics without touching the workbook.

Integration: You can use the result inside a PivotChart by creating a helper column “DomesticFlag” that uses:

=--(RIGHT([@TrackingID],2)="US")

Then the PivotTable can sum DomesticFlag to show domestic counts by warehouse.

Performance: COUNTIF reading 3,500 cells is trivial. In workbooks with 200,000 rows, use a dedicated calculation sheet and convert volatile functions (e.g., INDIRECT) to static references to keep recalc speed high.

Example 3: Advanced Technique

Scenario: You maintain a multilingual customer list in [B2:B50000]. Each record ends with a two-letter ISO country code (-US, ‑FR, ‑DE). You need a dynamic summary that lists every unique country suffix and its count, sorted descending. Additionally, you require case-sensitive matching because some codes overlap with product versions (-us vs ‑US).

Advanced solution using newer dynamic-array functions:

1. Extract unique suffixes (two characters after the last dash).

=LET(
 data,B2:B50000,
 suffixes,RIGHT(data,2),
 UNIQUE(FILTER(suffixes,suffixes<>""))
)

2. Count each suffix with case-sensitive SUMPRODUCT:

Assuming the unique list spills starting in [D2]:

In [E2] enter and spill:

=BYROW(D2:#,LAMBDA(sfx,
  SUMPRODUCT(--(RIGHT($B$2:$B$50000,2)=sfx))
))

3. Sort combined list by count:

=LET(
u,D2:#,
c,E2:#,
 SORT(HSTACK(u,c),2,-1)
)

Explanation:

• LET stores intermediate arrays to minimize recalculation overhead.
• BYROW iterates through each unique suffix and feeds it to SUMPRODUCT for a precise count.
• RIGHT works in an array context returning 50,000 substrings simultaneously.
• HSTACK merges the two columns before sorting on the second column in descending order.

Error handling & edge cases:

• If some entries are shorter than two characters, RIGHT returns the entire string, which may skew uniqueness. Wrap data in FILTER to exclude LEN less than 2.
• BYROW requires Microsoft 365. In older versions, fall back on helper columns plus COUNTIF.

Professional tip: Convert the final LET formula to VALUES to freeze the summary for weekly archival reports, preserving performance on historical tabs.

Tips and Best Practices

1. Use Tables or dynamic named ranges so COUNTIF ranges expand automatically when new data is appended.
2. Store suffix criteria in separate cells; this enables quick scenario analysis and reduces error risk from typos inside formulas.
3. Combine suffix counts with conditional formatting—e.g., highlight any SKU that ends with “-DISC” so visual checks align with numeric counts.
4. For dashboards, wrap COUNTIF inside IFERROR to display zero instead of #VALUE! when source ranges are temporarily empty during data refreshes.
5. Document wildcards in a note or data dictionary; future maintainers may forget that “*-CA” relies on the asterisk.
6. When counting multiple suffixes, prefer COUNTIFS with separate criteria arguments rather than concatenated OR logic in SUMPRODUCT; this keeps formulas transparent and fast.

Common Mistakes to Avoid

1. Omitting the asterisk: Writing COUNTIF(range,\"-DISC\") will only match cells that consist solely of “-DISC”, not those that end with it. Always include “*”.
2. Accidental case mismatches: COUNTIF treats “DISC” and “disc” as identical. If the data is case-sensitive (rare but possible), switch to SUMPRODUCT+RIGHT.
3. Hidden trailing spaces: “SKU-DISC ” (note the space) will fail to match. Apply TRIM or CLEAN before counting or wrap your criteria in TRIM too.
4. Expanding entire columns on large sheets: COUNTIF(A:A,\"*-US\") scans 1,048,576 rows. Restrict to the populated range or a Table to avoid slow recalc.
5. Forgetting to escape literal asterisks and question marks. If you literally want to match a cell ending in “END”, criteria must be \"~*END\".

Alternative Methods

While COUNTIF is king for simplicity, other tools deserve consideration:

Choosing a method often balances performance, maintainability, and compatibility with coworkers’ Excel versions. Start with COUNTIF; move up the ladder when requirements dictate.

FAQ

When should I use this approach?

Use suffix counting when naming conventions embed category information at the end of identifiers—for example, product statuses, country codes, or version tags—especially when decisions or metrics depend on those categories.

Can this work across multiple sheets?

Yes. Wrap each COUNTIF in a SUM on the summary sheet:

=SUM(
 COUNTIF(Sheet1!A:A,"*-DISC"),
 COUNTIF(Sheet2!B:B,"*-DISC")
)

Alternatively, create a 3D reference only when ranges align perfectly across sheets:

=SUMPRODUCT(COUNTIF('Jan:Dec'!A2:A100,"*-DISC"))

What are the limitations?

COUNTIF is case-insensitive and cannot natively accept regular expressions. Also, criteria longer than 255 characters trigger an error. For extremely large datasets (hundreds of thousands of rows), volatile formulas elsewhere can slow recalculation.

How do I handle errors?

• Wrap formulas in IFERROR to catch #VALUE! caused by wrong data types.
• Validate that the range is text by using ISTEXT; convert numbers with TEXT or VALUE as appropriate.
• Use LEN to debug unexpectedly long strings that do not match.

Does this work in older Excel versions?

COUNTIF with wildcards has been stable since Excel 97. SUMPRODUCT and RIGHT are also universal. Dynamic array functions (FILTER, LET, BYROW) require Microsoft 365 or Excel 2021. For Excel 2010 users, stick to COUNTIF, COUNTIFS, and helper columns.

What about performance with large datasets?

• Restrict ranges to populated cells or convert to Excel Tables.
• Avoid volatile functions (NOW, TODAY, INDIRECT) in the same sheet.
• When using SUMPRODUCT, add the double unary (--) to convert Boolean to numbers efficiently, and avoid unnecessary array multiplication.
• Consider moving heavy processing to Power Query or a database if row counts exceed 500,000.

Conclusion

Counting cells that end with a specific substring is a small but mighty skill. Whether you need to monitor discontinued products, classify shipments, or audit country codes, mastering these formulas keeps your reports accurate and automated. COUNTIF with a wildcard delivers the quickest solution, while SUMPRODUCT, FILTER, and Power Query offer precision and scalability for advanced needs. Practice the examples above on your own data, refine input hygiene, and soon suffix-based analytics will be second nature—another step toward Excel mastery.