Count unique distinct values that meet multiple criteria in excel
This post demonstrates how to calculate unique distinct products (Column E) and meeting a criterion or multiple criteria.
Table of Contents
- How many unique distinct products did Salesperson Jennifer sell?
- How many unique distinct products did Jennifer sell in January?
- How many unique distinct products did Jennifer sell in January and in region South?
- How many unique distinct products was sold in the south or in January?
Example 1,
How many unique distinct products did Salesperson Jennifer sell?
Array formula in cell E2:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Explaining array formula in cell E2
Step 1 - Calculate unique distinct products that Jennifer sold
=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
1/COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)
becomes
1/{4; 2; 3; 4; 0; 3; 4; 3; 4; 3; 0; 3; 2; 3; 2; 2; 2; 4; 4; 4; 4; 3}
and returns
{0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}
Step 2 - Filter Jennifers products
=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0)
becomes
IF({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}, 0)
and returns
{0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0}
Step 3 - Sum array
=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
becomes
=SUM({0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0})
and returns 4 in cell E2.
Example 2,
How many unique distinct products did Jennifer sell in January?
Array formula in C3:
Example 3,
How many unique distinct products did Jennifer sell in January and in region South?
Array formula in D3:
Example 4
How many unique distinct products was sold in the south or in January?
Array formula:
Download excel sample file for this tutorial.
Count unique distinct values meeting criteria.xlsx
(Excel 2007 Workbook *.xlsx)
Recommended blog posts
- Count unique distinct values in a column in excel
- Count unique distinct records in excel 2007
- How to extract a unique distinct list from a column in excel
- Filter unique distinct records in excel 2007
Related posts:
Sum unique distinct invoices in excel 2007
Count unique distinct records with a date and column criteria in excel 2007
Count unique distinct values using date criteria in a range in excel
Count unique distinct records in excel 2007
How to create a unique distinct list where other columns meet two criteria
Recommended categories
I am a convience store owner that is looking to make a spreadsheet formula. I want this formula to use information from one spreadsheet to auto-populate another spreadsheet on the next tab. I want the date the purchase was made, the consumer, and however many items the consumer purchased to equal one transaction on the other spreadsheet. Your help with this would be greatly appreciated.
Rodney Schmidt,
Read this post:
Auto populate a sheet
How can I re-write the following using SUMPRODUCT in Excel 2003?
(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)
Gabriel,
I don´t think you can!
Excel 2007 array formula:
=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
Excel 2003 array formula:
=SUM(--(FREQUENCY(IF("Jennifer"=$D$5:$D$26, COUNTIF($E$10:$E$31, "<"&$E$10:$E$31), ""), COUNTIF($E$10:$E$31, "0))
Hello Oscar,
Firstly thank you very much for this.
I am using this formula for Excel 2003 but have found that it is only counting the number of "Jennifers" and not the number of distinct/unique products that she has sold.
I wasn't sure whethere the 0 at the end needs to have quotes around it. But either way I get the same result. Am I missing something?
Thanks,
Javaney
Is there anyway to do an "or" as in this many unique products was sold in the south or in January?
In other words i would like to say count unique values based on the same criteria found in column A or B. thanks.
Jordan,
Great question!
See attached file:
Count-unique-distinct-values-jordan.xlsx
In Example 1, I've copied the formula and the data exactly as shown and get #VALUE! error. I'm using Excel 2007. Sorry for rookie question but it's key for me.
Rick Gonzales,
Did you create an array formula?
There was a problem between the chair and the keyboard - thanks.
Rick Gonzales,
Oscar,
I am using the following modification to your post as an array formula but it is slowing down Excel to a snail's pace:
=SUMPRODUCT((Data!$J:$J>='Analysis by Form Type'!L$1)*(Data!$J:$J<'Analysis by Form Type'!O$1)*(Data!$L:$L'HIDDEN DATA VALIDATE'!$B$33)*(Data!$E:$E='Analysis by Form Type'!$C3))
>='Analysis by Form Type'!L$1 is the start of a week.
<'Analysis by Form Type'!O$1 is the end of a week
='Analysis by Form Type'!$C3 is the Title of a form
The formula counts the number of errors on a given form within a given week. There are approximately 50 different types of forms, and each form may have multiple instances of use and multiple errors on each form, so the following formula is in an adjacent cell to give me the count of individual forms by type of form:
=SUM(IF(FREQUENCY(IF(Data!$E:$E=$C3,(IF(Data!$J:$J='Analysis by Form Type'!L$1,Data!$B:$B))))),Data!$B:$B)>0,1))
Because of the size of the data and the number of indivdual calculations, Excel just crawls through the data, but the results are correct.
Is there a cleaner way of performing these calculations? Maybe a UDF? I know a little about VBA but not enough to tackle this. Any help is greatly appreciated.
Hi Oscar,
In example 1, imagine one day Jennifer doesn't sell any product so the cell is blank.
It returns blank as a unique product (adding it to the sum) when it shouldn't. Do you know how can I not add it?
Thank you in advance.
Best regards,
Rui,
I get #DIV/0 error if there is a blank cell (product)?
Try this:
=SUM(IF(("Jennifer"=$D$5:$D$26)*(E5:E26<>""), 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)) ,0))
Hi,
That solved one of my problems, thanks! But I can seem to use that formula with thousands of rows.
You have 26 rows, imagine you have 10000. Do you know if there is a limit to that formula?
Rui,
Do you know if there is a limit to that formula?
I guess your cpu speed and computer memory.
Excel 2013 can calculate distinct values with criteria:
Distinct Count in Pivot Tables – Finally in Excel 2013
Is there a way to calculate how many times each Sales person sold a specific Product, say Product CC?
Jamie,
Sure!
Formula:
=SUMPRODUCT((D6:D27="Jennifer")*(E6:E27="Product CC"))
Perfect. Thanks, Oscar!
[...] change the cell references to suit your actual layout. The above formula was taken from here..... Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Exc... *Have I made an error with Mumbai, or did you in your example results?* I hope that helps. Good [...]
[...] the range F2:K10 with headers UNIQ. Take a look here for an explanation on the above formulas.... Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Exc... I hope that helps. [...]
[...] will obviously have to change the cellreferences to suit your layout!! Solutions found here.... Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Exc... I hope that helps. [...]
Hello Oscar, thank you very much for the tutorial!
Will you please tell me how I would go about calculating how many distinct products did Jennifer sell in the South AND the North?
Yay! I figured it out, all thanks to your Excel file. Thank you!!!
{=SUM(--(FREQUENCY(IF(("Jennifer"=$D$10:$D$31)*("North"=$C$10:$C$31)+("South"=$C$10:$C$31),COUNTIF($E$10:$E$31,"<"&$E$10:$E$31),""),COUNTIF($E$10:$E$31,"0))}
Carrie Hui,
Thanks for posting the answer!