You are here:

# Excel/Counting unique values that meet multiple criteria

Question

DP file
Hello Mr Pieterse,

I'm trying to find a single formula that count the total number of unique and distinct values in a spreadsheet , at the same time, meeting multiple criteria.

Attached below is a file which I was working on. Based on the file, I have a worksheet with column A 'cab №' which relates to Cabinet Number and Column B 'DP №'. Each Cab No, i.e., BS04 and BS07 have each four types of DP № which is 1,2,3 and 4 respectively.

Basing on the information above, i need to find out the total DP quantity. In other words,i need to find out the total number of distinct values in column 'DP No' that meet the criteria in Column 'cab №'. In the case above, the total DP quantity should be 8 as each criteria which is the 'cab №' BS04 and BS07 have each four types of DP  № (1,2,3 and 4).

However if ever there is repetitive information, the formula should not count it. An example relating to the file attached would be:information in the cell A2 'BS04' with 'DP No' of value '1' have been included by error in row 10. Thus, this information should be regarded as a duplicate and should not be counted.

I would be grateful if you could help me find a single formula that perform this operation in excel.

Thank you.
Lennie Ballah

The easiest way:
- add a column with this formula:

=A2&"|"&B2
(suppose this is column C)

Now use this array formula to count the uinique combinations:

=SUM(1/COUNTIF(A2:A10,A2:A10))

(enter using control+shift+enter, which will add {} around the formula)
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Jan Karel Pieterse

##### Expertise

Excel and Excel/VBA questions

##### Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...