Michael read the article How to count duplicates and unique values in Excel hoping for a solution, but it didn’t quite work for him. He has quote records that span three years. In any given year, quotes may occur more than once. He needs to know how many unique quotes occur for each year. The expression Michael found in the article mentioned above counts unique values–but the expression doesn’t do so conditionally. I came up with a super easy solution, but it wasn’t what Michael needed, so he came up with an array function that worked better for him. In this article, we’ll look at both solutions.
You might not have such a complex problem, but there’s a lot to learn here. Specifically, you’ll learn how to create a unique list using an advanced filter–that’s something almost all of us can use. You’ll also learn about the COUNTIF() function and see an array function at work. All of these are pieces of a solution that you can use in other ways.
The browser edition of Excel will support the existing file, but the advanced filter feature isn’t available. That version will support an existing filter, but you can’t implement it in the browser. The array function will update in the browser.
I’m using Excel 2016 on a Windows 10 64-bit system. Both solutions should work in any Ribbon version. You can work with your own data or download the demonstration .xlsx file.
SEE: Windows 10 power tips: Secret shortcuts to your favorite settings (Tech Pro Research)
My easy way
Figure A shows a simplified example that fits Michael’s scenario. It’s easy to see that there are three unique quotes and that 2016 has two unique quotes and 2017 has three unique quotes. The expression from the previously linked article returns 3, but that’s as far as it goes.
Figure A
Michael needs to know how many unique quotes occur in each year.
My simple solution is to filter a unique data set by the year and use an input value to do the rest; unfortunately, it isn’t dynamic. As the data exists, every record is unique because the dates are unique. We need a helper column that returns only the year. To accomplish this, insert a column between the quotes and the dates as follows:
- Select column C.
- Right-click the selection and choose Insert.
- In C3, enter the following function to return the year for that quote:
=YEAR(D3) - Copy the expression to the remaining data set as shown in Figure B.
Figure B
We’ll use the year to create a list of unique quote records.
After creating a column of years, we can generate a list of unique records as follows:
- Click inside the data set.
- Click the Data tab and then click Advanced in the Sort & Filter group.
- In the resulting dialog, click the Copy To Another Location option.
- Check the List Range reference and change it to reference only columns B and C. If you include the full dates in column D, the results will contain the full data set.
- Enter an anchor cell, F2, for the unique list in the Copy To control.
- Check the Unique Records Only option (Figure C).
- Click OK. Figure D shows five unique records.
Figure C
These settings will create a unique list of records.
Figure D
We’ll filter this unique list by years.
At this point, you could apply a filter to the unique data set and count the records using SUBTOTAL(). However, you must apply the filter every time you want to count unique quotes by the year. That’s not a big deal, but it’s still more work than necessary. Instead, let’s use an input value for the year and an expression that will return the number of unique records for the input year.
Putting the input value and the expression below the unique data is problematic because it’ll be difficult to spot, and you’ll have to move it every time you update the unique data set. So I entered two rows above both data sets. When applying this to your own work, be mindful of other data in the sheet. In this case, adding two rows above won’t hurt a thing, but that won’t always be the case. To insert two rows, select rows 1 and 2, right-click the selection, and choose Insert from the resulting submenu. Next, enter the following:
F1: Year
F2: ="Unique for " & G1
G2: =COUNTIF(G5:G9,G1)
Now you’re ready to see how it all works together. Enter an input value in G1–the value 2016 or 2017. As you can see in Figure E, the expression in G2 updates by evaluating the input value (the year) in G1. The concatenated expression in F2 serves as a label that identifies the year.
Figure E
Enter an input value to update the expression in G2.
SEE: 30 things you should never do in Microsoft Office (free TechRepublic PDF)
Michael’s expression
As I mentioned, my solution is easy to implement, but it isn’t dynamic. If Michael enters a new quote in the data set, he must update the unique record list. He found an array expression that worked better for him:
{=SUM(--(FREQUENCY(IF(quote<>"",IF(year=yearinputvalue,MATCH(year,year,0))),ROW(year)-ROW(firstcellinquoterange)+1)>0))}
As you can see in Figure F, it works great!
Figure F
An array function gave Michael the instant results he needs.
Michael decided to use an array, so that he can have a single value for each year, ready to go. His array isn’t dynamic though; if he adds a record, the array won’t evaluate it. In this case, the fix is simple–convert the original data set to a Table object. When you add a record, the references in the expressions (D1:D2) update automatically.
Some of you might be wondering why I consider the array function harder than the advanced filter. Certainly they can be more efficient if you know what you’re doing. However, many users don’t have the skills to write one, let alone implement and manage it. The only issue to consider beyond skill is that arrays tend to be memory hogs. But with today’s high-powered systems, that’s much less an issue than it used to be.
And there’s one more…
To be comprehensive, I want to mention one more possible solutions: a matrix. I won’t provide step-by-step instructions because the solution is in the downloadable demonstration file. The simple matrix shown in Figure G is similar to my filtering solution, but it uses a small matrix instead. If you have many conditional values to express, you might find a matrix easier to use.
Figure G
This matrix is similar to the unique list and input value solution.
The formulas follow:
Column G: =COUNTIFS(Table22[Quote],$F3,Table22[Year],G$2)
Column H: =COUNTIFS(Table22[Quote],$F3,Table22[Year],H$2)
Row 6: =COUNTIF(G$3:G$5,"<>"&0)
In this case, the original data set is a Table object, so the functions reference the Table columns. If you’re not familiar with Table referencing, read Use Excel’s table nomenclature to quickly create dynamic formulas. Note that the matrix isn’t dynamic.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Also read…
- Special report: Turning big data into business insights (ZDNet/TechRepublic free PDF)
- (TechRepublic)
- How to use VBA to sum Excel values by fill color (TechRepublic)
- Excel errors: How Microsoft’s spreadsheet may be hazardous to your health (ZDNet)
Daily Tech Insider Newsletter
Stay up to date on the latest in technology with Daily Tech Insider. We bring you news on industry-leading companies, products, and people, as well as highlighted articles, downloads, and top resources. You’ll receive primers on hot tech topics that will help you stay ahead of the game.
Delivered Weekdays