Counting unique order numbers with specific suffixes in Excel

Other Questions Google Sheets
1

Hey everyone, I’m stuck on a spreadsheet problem. I need to count unique order numbers in a column that has other data mixed in. The tricky part is that I only want to count each order number once, even if it appears multiple times.

The order numbers all have the same suffix at the end. I thought I could use this to filter them out, but I’m having trouble combining the unique and contains criteria.

I’ve tried using a formula like this:

=COUNTIF(Sheet1!A:A, UNIQUE("*ending"))

But it’s not giving me the right results. Can anyone help me figure out how to count only the unique order numbers with a specific ending? Thanks in advance for any tips!

2

I’ve encountered this issue in my work as well. A solution that’s worked for me is using a combination of UNIQUE and FILTER functions, then counting the results. Here’s the formula:

=COUNTA(UNIQUE(FILTER(A:A,RIGHT(A:A,6)=“ending”)))

This first filters the column for cells ending with “ending”, then extracts unique values, and finally counts them. It’s efficient and doesn’t require helper columns.

If you’re using an older version of Excel without FILTER, you can achieve similar results with array formulas:

=SUM(IF(FREQUENCY(IF(RIGHT(A:A,6)=“ending”,MATCH(A:A,A:A,0)),ROW(A:A))>0,1))

Remember to enter this as an array formula with Ctrl+Shift+Enter. This approach might be slower on large datasets, but it’s a solid alternative.

3

I’ve tackled a similar issue before, and here’s what worked for me:

Try using a combination of COUNTIFS and SUMPRODUCT. First, create a helper column with this formula:

=IF(AND(RIGHT(A1,6)="ending",COUNTIF($A$1:A1,A1)=1),1,0)

This checks if the cell ends with “ending” and if it’s the first occurrence.

Then, in another cell, use:

=SUM(helper_column)

This will give you the count of unique order numbers with the specific ending.

It’s a bit more involved, but it’s reliable and doesn’t require array formulas. Hope this helps!

4

hey there! have u tried using a pivot table? it’s super easy. just select ur data, go to insert > pivot table, then drag the order number field to rows and values. in values, change it to ‘count distinct’. then u can filter for orders ending with ur suffix. works like a charm for me!