Count unique distinct values that meet multiple criteria
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an Excel 365 dynamic array formula that counts items based on a single condition.
What's on this page
- Introduction
- How to count unique distinct items based on a condition and a date condition - Excel 365?
- How to count unique distinct items based on two conditions and a date condition - Excel 365?
- How to unique distinct products using OR logic - Excel 365?
- Earlier Excel versions
- Get Excel *.xlsx file
1. Introduction
What are unique distinct values?
Unique distinct values are all values except that duplicates are merged into one value, in other words, duplicates are removed.
Is a Pivot Table easier?
Yes, in most cases. I highly recommend you use a pivot table if you own Excel 2013 or a later version.
A pivot table is easier to work with and much faster if you have lots of data:
Count unique distinct values [Pivot Table]
The downside is that you need to manually refresh the pivot table if the source data range changes. You also need to learn how to create and operate Pivot Tables before you can count unique distinct values, however, it is worth it.
When to use formulas in these particular cases?
An array formula is great for an interactive dashboard or dynamic data meaning data changes often, like once a week or perhaps once a month.
My source data range changes often in size, how do I create the formulas more dynamic?
If you use an Excel defined Table or a dynamic named range you can quickly change the data range without editing the cell references in the array formula.
2. How to count unique distinct items based on a condition and a date condition - Excel 365?
This example shows how to count unique distinct products based on a given salesperson and a given month. Cell range B6:B27 contains dates, C6:C27 contains regions, however, they are not used in this particular example right now. Cell range D6:D27 contains salespersons, E6:E27 contains products.
The date condition is January and the salesperson condition is Jennifer. The formula extracts products from E6:E27 based on these criteria and then returns the count.
Excel 365 dynamic formula in cell C3:
An Excel dynamic array formula is entered as a regular formula, however, it spills values below the cell if needed. This example returns a single value so no need for spilling.
The image above shows products that match the criteria with a different cell background, Product AA and Product BB have duplicates so there are only three unique distinct products. The formula in cell C3 contains the formula and it returns number 3.
2.1 Explaining formula in cell C3
Step 1 - Find dates in January
The MONTH function extracts the month as a number from an Excel date.
Function syntax: MONTH(serial_number)
MONTH(B6:B27)=1
returns {TRUE; TRUE; TRUE; ... ; FALSE}
Step 2 - Find salesperson Jennifer
The equal sing is a logical operator that lets you compare value to value, it returns boolean value TRUE if the match and FALSE if not.
D6:D27="Jennifer"
returns {TRUE; TRUE; TRUE;... ; FALSE}
Step 3 - Which rows have both conditions met
The asterisk lets you multiply boolean values meaning applying AND logic between values on the same row. The parentheses let you control the order of operation which is very important.
(MONTH(B6:B27)=1)*(D6:D27="Jennifer")
returns {1; 1; 1; ... ; 0}
Step 4 - Filter products based on criteria
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer"))
returns {"Product CC"; "Product AA"; "Product BB"; "Product CC"; "Product BB"}
Step 5 - Extract unique products
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer")))
returns {"Product CC"; "Product AA"; "Product BB"}
Step 6 - Count rows in array
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
ROWS(UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer"))))
becomes
ROWS({"Product CC"; "Product AA"; "Product BB"})
and returns 3.
3. How to count unique distinct items based on two conditions and a date condition - Excel 365?
This example demonstrates a formula that counts unique distinct products based on a given salesperson, a given month, and a given region. Cell range B6:B27 contains dates, C6:C27 contains regions, D6:D27 contains salespersons, and E6:E27 contains products.
The date condition is January, the salesperson condition is Jennifer, and the Region is South. The formula extracts products from E6:E27 based on these criteria and then returns the count.
Excel 365 dynamic formula in cell C3:
An Excel dynamic array formula is entered as a regular formula, however, it spills values below the cell if needed. This example returns a single value so no need for spilling.
The image above shows highlighted products that match the criteria , Product CC has a duplicate so there are only two unique distinct products. The formula in cell C3 contains the formula and it returns number 2.
3.1 Explaining formula in cell C3
Step 1 - Find dates in January
The MONTH function extracts the month as a number from an Excel date.
Function syntax: MONTH(serial_number)
MONTH(B6:B27)=1
returns {TRUE; TRUE; TRUE; ... ; FALSE}
Step 2 - Find salesperson Jennifer
The equal sing is a logical operator that lets you compare value to value, it returns boolean value TRUE if the match and FALSE if not.
D6:D27="Jennifer"
becomes
{"Jennifer"; "Jennifer"; "Jennifer"; ... ; "John"}="Jennifer"
and returns
{TRUE; TRUE; TRUE; ... ; FALSE}
Step 3 - Find region south
C6:C27="South"
returns {TRUE; FALSE; FALSE; ... ; TRUE}
Step 4 - Which rows have all three conditions met
The asterisk lets you multiply boolean values meaning applying AND logic between values on the same row. The parentheses let you control the order of operation which is very important.
(MONTH(B6:B27)=1)*(D6:D27="Jennifer")*(C6:C27="South")
returns {1; 0; 0; ... ; 0}
Step 5 - Filter products based on criteria
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer")*(C6:C27="South"))
returns {"Product CC"; "Product CC"; "Product BB"}
Step 6 - Extract unique products
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer")*(C6:C27="South")))
returns {"Product CC";"Product BB"}
Step 7 - Count rows in array
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
ROWS(UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)*(D6:D27="Jennifer")*(C6:C27="South"))))
returns 2.
4. How many unique distinct products were sold in region south or in January?
This Excel 365 formula in cell C3 counts the number of unique distinct products in cell range E6:E27 if the corresponding cells on the same row in C6:C27 equals "South" or dates in B6:B27 is in January.
This example is different than the examples above, this example uses OR logic whereas the examples above uses AND logic.
Excel 365 dynamic formula in cell C3:
This Excel 365 formula is entered as a regular formula, column F in the image above shows the unique distinct count and if an item is a duplicate beginning from top to bottom.
4.1 Explaining formula in cell C3
Step 1 - Find dates in January
The MONTH function extracts the month as a number from an Excel date.
Function syntax: MONTH(serial_number)
MONTH(B6:B27)=1
returns {TRUE; TRUE; TRUE; ... ; FALSE}
Step 2 - Find region South
The equal sing is a logical operator that lets you compare value to value, it returns boolean value TRUE if the match and FALSE if not.
returns {TRUE; FALSE; FALSE; ... ; TRUE}
Step 3 - Which rows have at least on of the conditions met
The plus sign + lets you add boolean values meaning applying OR logic between values on the same row. The parentheses let you control the order of operation which is very important.
(MONTH(B6:B27)=1)+(C6:C27="South")
returns {2; 1; 1; ... ; 1}
Step 4 - Filter products based on criteria
The FILTER function extracts values/rows based on a condition or criteria.
Function syntax: FILTER(array, include, [if_empty])
FILTER(E6:E27,(MONTH(B6:B27)=1)+(C6:C27="South"))
returns {"Product CC"; "Product AA"; "Product BB"; ... ; "Product BB"}
Step 5 - Extract unique products
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)+(C6:C27="South")))
returns {"Product CC"; "Product AA"; "Product BB"; "Product EE"}
Step 6 - Count rows in array
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
ROWS(UNIQUE(FILTER(E6:E27,(MONTH(B6:B27)=1)+(C6:C27="South"))))
returns 4.
5.1. How to count unique distinct items based on a condition and a date condition?
This example works with Excel versions prior to Excel 365, it describes a formula that counts the unique distinct products Jennifer sold in January? In other words, it extracts a unique distinct list based on three conditions.
Cell range B6:B27 contains dates, C6:C27 contains regions, D6:D26 contains salespersons, and E6:E27 contains items.
Array formula in C3:
This is an array formula and is entered differently than a regular formula. To enter an array follow these steps:
- Double press with left mouse button on the destination cell, a prompt appears.
- Type or copy/paste the formula.
- Press and hold CTRL and SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
A beginning and ending curly bracket appears like this:
{=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0))}
Don't enter the curly brackets yourself, they appear automatically if you followed the above steps.
5.1.1 Watch this video where I explain how the above formula works
This calculation is also possible using a pivot table, you simply add more criteria: Count unique distinct values [Pivot Table]
5.1.2 Explaining formula in cell C3
Step 1 - Find values meeting first condition
The equal sign compares the condition (Jennifer) to all cell values in $D$6:$D$27 and returns an array containing TRUE or FALSE (boolean values).
("Jennifer"=$D$6:$D$27)
returns {TRUE; TRUE; TRUE; ... ; FALSE}
Step 2 - Find values meeting the second condition
The less than < and equal sign together lets you compare Date 1/31/2011 with dates in $B$6:$B$27, it returns TRUE if the date is earlier than or equal to 1/31/2011.
($B$6:$B$27<=DATE(2011, 1, 31))
returns {TRUE; TRUE; TRUE; ... ; FALSE}
Step 3 - Calculate the number of records that contain condition 1 and 2 and any products
The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions. There is a criteria range and a condition forming a pair.
| Pair | Criteria range | Criteria |
| 1 | $D$6:$D$27 | "Jennifer" |
| 2 | $E$6:$E$27 | $E$6:$E$27 |
| 3 | $B$6:$B$27 | "<="&DATE(2011, 1, 31) |
1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31)))
returns {0.5; 1; 0.5; ... ; 0.5}
Step 4 - If condition 1 and 2 are TRUE then return numbers from step 3
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)
You get the numerical equivalents if you add or multiply arrays, the numerical equivalent of TRUE is 1 and FALSE is 0 (zero),
returns {0.5; 1; ... ; 0}
The image above shows the results of each step.
- Step 1 - Column G
- Step 2 - Column H
- Step 3 - Column I
- Step 4 - Column J
Step 5 - Sum numbers in array
SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
returns 3 in cell C3. There are three unique distinct products based on two conditions.
5.2. How many unique distinct products did Jennifer sell in January and in region South?
How many unique distinct products did Jennifer sell in January and in region South?
Array formula in D3:
5.3. How many unique distinct products was sold in the south or in January?
How many unique distinct products was sold in the south or in January?
Array formula:
Recommended article:
Recommended articles
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Recommended articles
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Count unique distinct values category
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Excel categories
107 Responses to “Count unique distinct values that meet multiple criteria”
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.
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!
Thank you for this formula! I searched through almost the ENTIRE internet looking for this answer. It's a real lifesaver and I will now be perusing the rest of your site. You're a genius.
Jim,
I am happy you like it!
Could you help me with (1) the Count the number of unique Divisions where ALL products have been delivered. (2) The Count the number of unique Divisions where NOT ALL products have been delivered. Determined by the presence or absence of a Delivery Date.
Three columns of data, listed below.
Division Product Delivered Date
AN9 CPPR.T014.AN9.QD.R00002.F090101.T130926.F001 28-Sep
GH3 CPPR.T014.GH3.QD.R00002.F090101.T130926.F001 29-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F001 30-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F002 29-Sep
L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F003
L53 CPPR.T014.L53.QD.R00002.F090101.T130926.F001
L7W CPPR.T014.L7W.QD.R00002.F090101.T130926.F001
NHP CPPR.T014.NHP.QD.R00002.F090101.T130926.F001
L3N CPPR.T014.L3N.QD.R00002.F090101.T130926.F001 24-Sep
WH3 CPPR.T014.WH3.QD.R00002.F090101.T130926.F001
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F001 25-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F002 25-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F003 26-Sep
L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F004 27-Sep
Stephen,
read this post:
Count unique distinct values with a condition
[…] Stephen asks: […]
very good mr oscar
Thank you
HI Oscar - the formula is great for fixed rows - however i am finding it difficult to replicate it to entire column, as soon as i hit Shit+Ctrl+enter - my excel hangs and never comes out. i had to wait 30 min and close it.
example below. - can you please help - i guess its a small missing piece. i think i am throwing it into a infinite loop of some kind
=SUM(IF(("Jennifer"=D:D)*(E:E""), 1/(COUNTIFS(D:D, "Jennifer", E:E, E:E)) ,0))
praneeth,
Try using smaller cell ranges or dynamic named ranges.
=SUM(IF(("Jennifer"=D:D)*(E:E""), 1/(COUNTIFS(D:D, "Jennifer", E:E, E:E)) ,0))
i guess the problem is with E:E,E:E in COUNTIFS and i did put not equal to sign in first if - for some reason the comments is not seeing it.
[…] had a 1, the audit before a 2, etc. That was a bit complicated, but with some help from this site (Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Exc…) I got the formula working and entered it in column G. It is an array formula that basically […]
Hi there,
I managed to reuse your formula, thank you!
However, what if I modified example 2 to be february? The only reason why your formula works for january is because there is no december.
I tried putting in AND() statements in the if and countif to show the range of 2011,2,1 to 2011,2,31 but I kept getting 0.
Essentially, how do I see example 2 but given a specific month (other than january since it is the first month in your range).
Thanks!
Alex Dorward,
However, what if I modified example 2 to be february? The only reason why your formula works for january is because there is no december.
I tried putting in AND() statements in the if and countif to show the range of 2011,2,1 to 2011,2,31 but I kept getting 0.
Try this:
=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 2, 28))*($B$6:$B$27>=DATE(2011, 2, 1)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 2, 28), $B$6:$B$27, ">="&DATE(2011, 2, 1))), 0)
[…] an array formula modified from Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Exc… […]
Thanks That was what I was looking for T_T you saved me
martin,
thank you!
Hello your question no 1 answer is now working when i do it, i copied the exact same data as in ur excel and also inserted in exact same column but still dosent work it show #VALUE error on top of the table and 0.5 on other places
Anup,
can you show us your formula?
Hello Oscar,
I have uploaded my excel image file to postimage.org
This is the url => https://postimg.org/image/5l3kw0lyz/
i used the same formula as u have provide
=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
Anup,
Enter the formula as an array formula. You will find instructions above, in the blog post.
It worked thanxs, what i did before was copy pasted the formula in the cell and pressed Ctrl+Shift and Enter but what i needed to do was paste the formula in the formula bar above, should have read the instruction more carefully, sorry for trouble, but thaxns for the help. :-)
Hi Oscar, is there anyway to adapt this to include/exclude filtered data? I have a linked data table with slicers and only want to count unhidden data.
Thanks in advance!
Hi Oscar,
This formula suits my requirement if i can replace the "Jennifer" part with a cell reference and & nd * around it.
=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)
I want to enter a text and search the same and give the unique count of entities in another column corresponding to the entered value.
Please help
Can I still use this formula if I want to reference an array on another worksheet in the same excel file?
Kristin,
Yes, you can.
Hello Oscar,
My question is if I can change the formula so that I will know the number of unique products sold each day, thank you!
[…] || []).push({}); Hello all, I am adapting an array formula that I read about from this link (great resource by the way). The purpose is to count the number of unique items in a column based […]
Hello,
It is possible to count/calculate unique distinct products that Jennifer and Laura sold. (Jennifer and Laura are in the same column "D" named Sales person)
I want to say, if Jennifer sold Product CC and Laura also Product CC, it will count only once. The result should be = 1.
If Jennifer sold Product CC and Product BB and Laura also Product CC and Product BB, it will count only once. The The result should be = 2
But if Jennifer sold Product CC, Product AA and Product EE And Laura Product CC, Product AA and Product BB
The result should be = 4 (they sold both Product CC, Product AA and + Jennifer sold Product EE and + Laura sold Product BB)
I will use later this Formula in accounting to automate my work instead of doing pivot table and copy paste the result.
Many thanks in advance.
Sskool,
Interesting question.
Array formula in cell B3:
=COUNT(1/FREQUENCY(IF(COUNTIF($F$2:$F$3, $D$6:$D$27), COUNTIF($E$6:$E$27, "<"&$E$6:$E$27), ""),COUNTIF($E$6:$E$27, "<"&$E$6:$E$27))) Get the Excel *.xlsx file Count-unique-distinct-values-meeting-criteria-Sskool.xlsx
Hi Oscar,
I just want to say thank you.
Hi, i do not mind. For me no problem.
Referencing Sample 3:
I would like the date value to be a variable in a cell reference so that the array returns all values <= to the date entered in the cell (using ActiveX Calendar). Thus, when the value for what is given in the date field changes the array returns only those values corresponding to the date provided.
Please advise how this can be done?
Hi, i will advise bellow
In the Cell C3 the array Formula:
=SUM(IF(($F$2=$D$6:$D$28)*($B$6:$B$28<=$F$3)*("South"=$C$6:$C$28), 1/COUNTIFS($D$6:$D$28, $F$2, $E$6:$E$28, $E$6:$E$28, $B$6:$B$28, "<="&$F$3, $C$6:$C$28, "South")), 0)
In the Cell F2, the value Jennifer and in the Cell F3: the date for example: 27/06/2015 and in the Cell H3 the ActiveX Calendar. The Cell H3 is linked to the Cell F3. So, when you choose the date in the Cell H3, the Cell F3 when be changed and the Cell C3 will be counted.
Hi, entended from example 2 - How many unique distinct products did Jennifer (and Laura) sell in January?
Hi, entended from example 2 - How many unique distinct products did Jennifer (and Laura) sell in January? Any suggested solution?
Am using Excel 2013 and found that (a) Your formulas and table when cut and pasted in works... when I use your data.
b) There is a {} around your formula also.
c) When I remove or modify, the {}, the formula does not work and returns 0.
d) When I try to recreate the same formula I get a #VALUE error.
Having some trouble. Any thoughts?
The idea is quite useful if I can get it right.
Thanks, Jonathan
Jen AA
Jon AA
Jen BB
Jon AA
Jen CC
Jen AA
Jon BB
Jen BB
Hal CC
Hal AA
Hal AA
Jon AA
Jen AA
Jon BB
Hal AA
Jen CC
Jon AA
Jen BB
Jon BB
Hal CC
Hi Jonathan,
It's not a typed {} it actually indicates an Array Formula. To use this code copy and paste the code but do not press enter, instead press ctrl+alt+enter together. This will add the brackets and complete the formula properly.
Thanks for the start of an answer, Shaun. When I am focused in the cell and type Ctrl+Alt+Enter, I cannot get out of the cell.
Still have #Value error.
Can you post the formula you're trying to use?
=SUM(IF("Hal"=A1:A20, 1/(COUNTIFS(A1:A20, "Hal", B1:B20,B1:B20)), 0))
My apologies Jonathan, it should be ctrl+shift+enter.
Muscle memory is better than my actual memory it would appear, I've checked and your formula works.
THANK YOU STUART!!
Hmmm. I've never come across this Ctrl+Shift+Enter having a different impact in excel. Where might I learn more about it. Feels like abstract geometry for a moment (the place where triangles as a rule do not have 180 degrees, etc ;-)
It's the only example I know of there being any alternative entry of formulas.
How and when you should use them and the rules around it I'm not sure of. There's lot's of info on the net on array formulas but probably best checking yourself as some of it is very difficult to interpret!
Again, thanks. By the way, could not edit my post and currently scrolling a lot so quickly mistyped your name. Thanks again for the help, Shaun!
I really am crazy ;-(
I can see now from better reading that the question of entering arrays to avoid VALUE ERROR or O was answered by Oscar in 2014 and best in the initial instructions. Sorry all!
I'm currently trying to add range condition. In other words, number of unique values for Jennifer in which her approval rating was (Column E) 0-25,26-50,51-75, or 76-100).
Thoughts while I reread and try to figure this?
Here is the answer to the rating question. I changed date to Rating (0-100) and created the following formula which shows Jennifer's sales for which she had a 25 or lower rating:
=SUM(IF(("Jennifer"=$D$35:$D$56)*($B$35:$B$56=0), 1/COUNTIFS($D$35:$D$56, "Jennifer", $E$35:$E$56, $E$35:$E$56, $B$35:$B$56, "="&0)), 0)
Could make same for 26-50, 51-75, and 76-100.
Thanks Oscar for having such a well organized site with a decent feedback thread so users could learn, post, and get feedback all in the same workday! And thanks to Shaun too!
CORRECTION - SOMETHING MISSING ABOVE, FIXED BELOW
Since some of the 0s could be confused, here is fixed rating for 0-25
=SUM(IF(("Jennifer"=$D$35:$D$56)*($B$35:$B$56=0), 1/COUNTIFS($D$35:$D$56, "Jennifer", $E$35:$E$56, $E$35:$E$56, $B$35:$B$56, "="&0)), 0)
and the one for 26-50
=SUM(IF(("Jennifer"=$D$35:$D$56)*($B$35:$B$56=26), 1/COUNTIFS($D$35:$D$56, "Jennifer", $E$35:$E$56, $E$35:$E$56, $B$35:$B$56, "="&26)), 0)
NOTICED THAT IN BOTH MY ABOVE REPLIES THAT THE RESULT IS SCRUBBED WHILE UPLOADING SO SOME OF TEXT IS MISSING &*^%!
In case this third try gets scrubbed, here is the full text below
https://www.postimage.org/image/ayilgdaxl
=SUM(IF(("Jennifer"=$D$35:$D$56)*($B$35:$B$56=26), 1/COUNTIFS($D$35:$D$56, "Jennifer", $E$35:$E$56,
$E$35:$E$56, $B$35:$B$56, "="&26)), 0)
{0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333;.....
Is this supposed to read 0.25, 0.5, 0.33333, etc.? If not, why is there a 0 paired with every value in the array?
Actually, in the file I used, the 25 refers to an integer from 0-99. So 25 is correct.
If you want to see the excel file, send me an email. My address is at my book website, https://www.endingschoolshootings.org.
Best,
Jonathan
If region is south, and date is january 1 - give me all product names for those two matching things.
How would I write that without hard coding the date and region?
I want to know all products sold on a given day in each region.
ie: for the south on january first what products sold.
I know your chart only has one product a day...
But lets say you had 4 different products sold on Jan1 in south region...how would the formula be able to pull that?
Regarding:
... 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$26)), 0))
Can you elaborate on why the $E$5:$E$26 is there twice?
Thanks,
-SC
Hi Oscar,
I was struggling with a similar requirement at work, and your solution fits like a glove.
I must really appreciate the brilliant simplicity and elegance of the logic you have used.
Regards
Pradeep.
Good morning Oscar -
I have reviewed your examples regarding "Count unique distinct values that meet multiple criteria in excel", but I can't seem to incorporate my criteria into your examples; especially the "E" Column $E$5:$E$26, $E$5:$E$26. Also, can the count be performed by Column (C:C), (D:D), etc. verses $C$2:$C$4049 (committed range).
Each term enrollment numbers change and I'm trying to allevate the step of changing range (in the formula) when I can simply copy/paste over the existing records with new data pulled and the formula counts the new data (by column) with the criteria in set formula.
EXAMPLE: =COUNTIFS('F2015'!G:G,"GR",'F2015'!K:K,"FG",'F2015'!DL:DL,"N") This formula is embedded in a cell that auto-populates within milliseconds the count of International First-Time Graduate Students when I copy/paste my new data.
*** I want a formula that will count records to get a Total Count of Foreign Countries Represented (UNDUPLICATED I.E. counted only once). Current Data file contains 4048 rows (student record per row). To do this, the following criteria applies. Any help you can provide would be most appreciated. Cris
SAMPLE DATA TABLE PROVIDED BELOW:
D:D CITIZENSHIP US
F:F ALIEN_STATUS = N
TOTAL FOREIGN COUNTRIES REPRESENTED = 9 (SEE DATA INFO BELOW)
PIVOT TABLE USING DATA SAMPLE BELOW.
ALIEN_STATUS N
Sum of JU_ID
CITIZENSHIP Total
AU 5
BR 22
BS 4
CA 14
DO 29
FR 21
GB 15
NG 10
VN 17
Grand Total 137
A B C D E F
JU_ID TERM LEVEL CITIZEN RES_CNTRY ALIEN_STATUS
1 15FALL UG US
2 15FALL UG
3 15FALL UG
4 15FALL UG BS BS N
5 15FALL UG AU AU N
6 15FALL UG CA CA N
7 15FALL UG US
8 15FALL UG CA CA N
9 15FALL UG US
10 15FALL UG NG NG N
11 15FALL GR US
12 15FALL GR US
13 15FALL UG US
14 15FALL UG US
15 15FALL UG GB GB N
16 15FALL UG US
17 15FALL GR VN VN N
18 15FALL UG US US C
19 15FALL UG US
20 15FALL UG US
21 15FALL GR FR FR N
22 15FALL UG BR BR N
23 15FALL GR PK US R
24 15FALL UG UA US R
25 15FALL GR
26 15FALL UG US PR U
27 15FALL UG PH US R
28 15FALL UG US VI U
29 15FALL UG DO DO N
30 15FALL GR US US C
31 15FALL UG US US C
32 15FALL UG US
Hi Cris
Also, can the count be performed by Column (C:C), (D:D), etc. verses $C$2:$C$4049 (committed range)
Each term enrollment numbers change and I'm trying to allevate the step of changing range (in the formula) when I can simply copy/paste over the existing records with new data pulled and the formula counts the new data (by column) with the criteria in set formula.
No, it will be too cpu intensive. You could however use dynamic ranges:
https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/
Array formula in cell H3:
=SUM(IF(G2=$F$2:$F$33,1/(COUNTIFS($F$2:$F$33,G2,$D$2:$D$33,$D$2:$D$33)),0))
Good morning Oscar -
Thank you for taking the time to help provide a sensible formula to cut down on time. At first, I couldn't get your formula to work. Then I realized that because I am pulling from multiply sheets, I needed to add the sheet names. I thought I would share my outcome since there are probably others working with multiple sheets and this one little step will save them time trying to figure out why they are getting "0". Works beautifully. Best Regards!
=SUM(IF(RESIDENCY!M77='F2015'!$DL$2:$DL$4049,1/(COUNTIFS('F2015'!$DL$2:$DL$4049,RESIDENCY!M77,'F2015'!$DG$2:$DG$4049,'F2015'!$DG$2:$DG$4049)),0))
Hi Oscar,
Really loved your article! How would you go about looking at a list of time stamps and id Numbers and filtering out the rows where the ID number and timestamps were duplicated?
Example: in column A we have ID numbers of 1, 2, 3, 4, 3, 2 and in column B we have time stamps of 10:30, 11:00, 12:00, 1:00, 12:00, and 11:00. So we need to remove the rows for ID number 2 and 3 and timestamp 12:00 and 1:00 (they are the same row). Thank you!
I can personally email you the file if need be.
Sapan
This regular formula in cell C1 counts records:
=COUNTIFS($B$1:$B$6,B1,$A$1:$A$6,A1)
Copy it to cells below.
A value above 1 tells you that there are duplicate records.
Hello,
This formula is almost working for me. I noticed that if I have #N/A values then the formula will not calculate a result. I do not want to include the #N/A values in my count, is there something that can be added to the formula to get the formula to calculate?
Thank you
Tara,
I am not sure if this is working but you could try the IFERROR function.
Hi Oscar,
I'm looking to automate some data. I want to count the amount of document submissions I work on. The columns that need to be taken into account are the month they were registered, the month they were approved, and the document type.
I want to know if it is possible to write a formula to count the amount of submissions that are processed for certain document types. For example, the amount of invoices that were registered or approved in the month of October, without duplication. Currently, I am manually counting this every month by using filters, so having it automatically count would be great.
Thanks :)
Hi Oscar,
I have tried to recreate your formula replacing the "" text fields with a cell reference. The unique values I am hoping to count are in KW Upload column A
E.g.
=SUM(IF((C$1='KW Upload'!$D$2:$D$1500)*($B2='KW Upload'!$H$2:$H$1500)*($A2='KW Upload'!$C$2:$C$1500),1/COUNTIFS('KW Upload'!$D$2:$D$1500,C$1,'KW Upload'!$A$2:$A$1500,'KW Upload'!$A$2:$A$1500,'KW Upload'!$H$2:$H$1500,$B2,'KW Upload'!$C$2:$C$1500,$A2)),0)
This returns a #value. Are you able to help?
Many thanks,
Tim
I tried your solution for the #Div/0! error and it did not work. Here is my formula.
=IF(A4>=TODAY(),"Future Date",IF(('VBM Stack Rank'!D$59:D800=A4)*('VBM Stack Rank'!AG$59:AG800"")*('VBM Stack Rank'!G$59:G800"")*('VBM Stack Rank'!D$59:D800""),1/(COUNTIFS('VBM Stack Rank'!D$59:D800,"="&A4,'VBM Stack Rank'!G$59:G800,'VBM Stack Rank'!G$59:G800,'VBM Stack Rank'!AG$59:AG800,"=Prospect")),0))
Please help!
Robin,
I believe this part of the formula returns 0.
1/(COUNTIFS('VBM Stack Rank'!D$59:D800,"="&A4,'VBM Stack Rank'!G$59:G800,'VBM Stack Rank'!G$59:G800,'VBM Stack Rank'!AG$59:AG800,"=Prospect"))
1/0 returns #DIV/0! error.
For an application I'm trying use this logic on, I need to be able to search a column for all entries that contain certain text. Can that be done using this equation? For example:
=SUM(IF("*J*"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "*J*", $E$5:$E$26, $E$5:$E$26)), 0))
This would look for duplicates from both John and Jennifer. In my dataset, I cannot feasibly add each field to the criteria individually and need to filter all that contain a common element.
Thank you.
Hi Oscar! Hoping you can help me figure out where my error is. My goal is to fill in cells B9-B12 in the top workbook using the data in the bottom workbook. Each of the rows in the “Offers & Social Links” tab is a separate offer within mailings that could occur 1-3x/week. I need to figure out how many offers were sent each week of each month. (If it is pulling correctly, Week 4 for January (cell B12) should fill in as 2). The link to my workbook images and formula is below. Thanks in advance for any insights!!
https://postimg.org/image/oiubtx0ub/6a93426d/
Hi Oscar
Is there a way to make the formula's ranges dynamic?
Here is my formula:
=SUM(--(FREQUENCY(IF(X3:X12=X1,MATCH(C3:C12,C3:C12,0)),ROW(C3:C12)-ROW(C3)+1)>0))
Jean,
yes it is possible:
https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/
[…] Rodney Schmidt asks: […]
Hi Oscar,
I have a question regarding the initial formula:
=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)
If a cell in the date column is empty, can the function be modified to work past this?
Hello, thank you very much for this formula
It is very useful and this is only place i have found it working!
Maryan,
thank you. I am happy you like it.
Hi Oscar,
I"m trying to use your original formula to count on our invoices report how many active clients our sales reps have per month.
I have column A that shows the month, column B that shows the sales rep name and column c that shows the customer name (customer's name repeat because they might have more than one invoice process in a month).
Example:
Month Sales Rep Customer Name Name 1 Name 2 Name 3
July Name 1 Clark C 1 3 2
July Name 2 Show B
July Name 3 WA
July Name 2 PDX 1
July Name 2 Show B
July Name 1 Clark C
July Name 3 CLK
July Name 3 CLK
July Name 2 GRANT
July Name 2 GRANT
Thank you!
I want the conditions to be flexible and if a condition is blank then returns the output based on the available conditions
[…] enter link description here […]
[…] enter link description here […]
Hello Oscar,
Thanks for the detailed records on your knowledge on functions.
I am looking for some solutions, to count unique distinct values which has to meet two criteria.
If using the examples above, that is I need to know, the sum of unique distinct unique products did each salesperson sold, without knowing their names in advance.
In my case, I need to consider two columns. Column A contains name of participant, Column B contains their phone number. I need to find out how many unique distinct participants has filled-out the form. But considering they may have the same name, so i cannot just count unique distinct values of Column A. I need to consider column B, their phone number as well. If their name are the same but phone number is different, then it should count as two unique distinct value instead of 1.
Please give me some advice on how should i setup my formulae.
Thanks a lot.
I am looking for a solutions to to count unique values in a date range but only count them if the meet certain criteria. for example I am using this formula =SUMPRODUCT(IF((Inventory!$B$2:$B$10000=A2), 1/COUNTIFS(Inventory!$B$2:$B$10000, "="&A2,Inventory!$H$2:$H$10000, Inventory!$H$2:$H$10000), 0))
Column B is has all my dates
Column H has people names
I want to add column AW that has a specific zone where the customer falls in. I want to be able to count the number of unique customers falls into a specific zone in a date range
Hi Oscar
i am trying to get the count of distinct numbers based on the Description provided in column B. I tried with the count ifs somewhere i am getting confused and not getting the answer. could you please help me
Require below details
Column A Column B
Description Count
SCM Require count of Column Number based on the data provided
Perfect
Reclass
AP
Merger
Changes Required
based on the below data
Data
Numbers Description
7100593008 SCM
7100600559 SCM
7100602897 SCM
7100602897 SCM
7100602897 SCM
7100605476 SCM
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
7100564950 Perfect
This formula helped me...Thank for this
Parally, can you pls provide details on how do i filter for other Month's
I think this formula is not helping for other month's (example i need to filter for a specific month from the list)
Appreciate your response on the formula