Usually, I divide this monthly question and answer column between two or three questions. This month, I'm devoting the entire column to one reader's question: Ashish wants to conditionally count unique values.
You can use a complex array formula, but they're hard to manage if you don't know what you're doing. If you prefer an array, use your favorite search engine to look for "conditional count of unique values in Excel." You'll find plenty of discussions on the topic. However, I'd like to offer two user-friendly solutions: a simple matrix and a PivotTable.
I'm using Excel 2013 on Windows 7. For your convenience, you can download the .xlsx file. There's no .xls file, because neither solution is supported by Excel 2003 or earlier.
Figure A shows a simple data set. As you can see, each record contains a name, a code, and a group. Ashish needs to extract the number of customer codes billed by each executive. An executive can repeat the same code, but the code number should be counted only once. In other words, he wants the number of unique customer codes for each executive.
We'll count unique customer codes for each executive.
This first solution counts the unique customer codes by executive by evaluating the executives against a unique list of customer codes in the form of a matrix. The first thing we need is a list of unique customer codes. If you have a stable list of unique values, use it. If not, you can extract a unique list of values from the natural data by using the Advanced Filter feature as follows:
- Click the Data tab.
- Click Advanced in the Sort & Filter group.
- In the resulting dialog, click the Copy to another location option.
- Click inside the List range control and then select B1:B14 in the sheet.
- Click inside the Copy to control, and then select any cell in the worksheet to anchor the unique list's range. I selected E2.
- Check the Unique records only option (Figure B).
- Click OK to generate the unique list of customer code values (Figure C). It won't matter that the codes aren't in any meaningful order.
After generating a custom list of customer codes, enter each executive (matching the names exactly) in F2:I2. Next, use the COUNTIFS() function to return the number of customer code values for each executive. To do so, enter the following formula in cell F3 and copy it to the rest or the matrix, as shown in Figure D:
This matrix counts customer codes by executive values.
This function evaluates the unique values in column E and row 2 against the data in columns A and B.
We still need a unique count though. To complete the solution, add a totaling row to the bottom of the matrix that counts the unique customer values. In this case, that means excluding the 0 values. Specifically, enter the following function in F8 and copy it to I8, as shown in Figure E:
Exclude 0s from the count.
Ashish has five records but only four unique customer codes. The others all have the same number of unique codes as they have records.
This solution has its limitations. Adding new records won't update the matrix. If you're using Excel 2007 or later, convert your data set into a Table object by clicking inside the data set, clicking the Data tab, and then clicking Table in the Tables group. Confirm whether the data set has headers, and click OK. As you can see in Figure F, the matrix automatically updates when referencing a Table data source.
The matrix updates if you use a Table to control data entry.
A second problem isn't as easy to solve. If you add a new customer code value to the data set (Table), you must also add it to the matrix. The count shown in Figure F changed, because the code in the new record is unique for Amit and the code is already in column E of the matrix.
If you're using Excel 2013, you can use the PivotTable feature's new Distinct Count option as follows:
- Click inside the data set, and then click Insert.
- Click PivotTable in Tables group.
- In the resulting dialog, be sure to check the Add this data to the Data Model option (Figure G), and click OK. Table2 in the Table/Range control is a reference to the data range. Remember, we converted the data to a Table object.
- Drag the Executive Name field to the Rows section.
- Drag the Customer Code field to the Values section.
- Click the Sum of Customer Code drop-down in the Values section and choose Value Field Settings (Figure H).
- Choose Discount Count from the Summarize value field by options (Figure I).
- Click OK to see the results (Figure J).
To hide the grand total row, click the contextual Design tab, and then choose Off for Rows and Columns from the Grand Totals drop-down in the Layout group.
A PivotTable is much simpler, but you must remember to refresh it when you update the data source. Earlier versions of Excel support the PivotTable feature, but Excel 2013 is the first to offer the Discount Count option.
There are probably many ways to count unique values, conditionally, without using an array. I've shown you two that are easy to implement. What advice would you give Ashish? Please share your ideas in the comments below.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. 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, nor do I ask for a fee from readers. You can contact me at email@example.com.
- The scoop on Excel's counting functions
- 10 reasons to use Excel's table object
- Combine Outlook templates with macros to eliminate repetitive tasks
- Generate multiple indexes in the same Word document
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.