Count unique distinct values based on a condition
The following article demonstrates how to construct a formula that counts unique distinct values based on a condition.
The image below shows a table in column B and C. Column B contains names and column C contains products. How many unique distinct products did Salesperson Jennifer sell?
The blue arrows shows unique distinct products based on sales person "Jennifer", the total number match with the number in cell E4. The remaining highlighted records are only duplicate values.
Array formula in cell E2:
Learn to build formulas that count unique distinct values based on criteria:
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this [โฆ]
Count unique distinct values that meet multiple criteria
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.
Recommended article:
Array formulas allows you to do advanced calculations not possible with regular formulas.
Explaining array formula in cell E2
You can follow along as I explain the formula, select cell E2. Go to tab "Formulas" on the ribbon, click on "Evaluate formula" button.
Click on "Evaluate" button shown in above image to move to next step.
Step 1 - Calculate unique distinct products that Jennifer sold
1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$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}
Recommended article:
How to use the COUNTIFS function
Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.
How to use the COUNTIFS function
Step 2 - Filter Jennifers products
IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$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}
Recommended article:
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 3 - Sum array
=SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$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.
Recommended article:
The SUM function in Excel allows you to add values, the function returns the sum in the cell it is [โฆ]
Download excel *.xlsx file
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Count unique distinct values in two columns
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 [โฆ]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 [โฆ]
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), [โฆ]
6 Responses to โCount unique distinct values based on a conditionโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
[โฆ] https://www.get-digital-help.com/2017/08/28/count-unique-distinct-values-based-on-a-condition/ [โฆ]
It seems as the counting formula can be a bit shorter.
{=SUM(((B5:B26="Jennifer"))/COUNTIFS(B5:B26,B5:B26,C5:C26,C5:C26))}
----------------------------
Michael (Micky) Avidan
โMicrosoftยฎ Answers" - Wiki author & Forums Moderator
โMicrosoftยฎ" Excel MVP โ Excel (2009-2018)
ISRAEL
Michael (Micky) Avidan,
Why didn't I think of that.
Thank you, I appreciate your comment.
Mickey,
and faster too. Amazing! Can you explain how it works?
Ed
i recreate your data to my excel sheet, and using your formula. why the result different with your result. mine give result div 0, and if i change the range it give result #value
thx
Andry
There are many things that could be wrong.
1. Did you enter the formula as an array formula?
2. Are the cell references correct?