## 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 page**

Unique distinct values are all values except that duplicates are merged into one value, in other words, duplicates are removed.

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]

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.

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.

### How to count unique distinct items based on a condition and a date condition?

How many unique distinct products did Jennifer sell in January?

**Array formula in C3:**

#### 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]

### 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)

becomes

"Jennifer"={"Jennifer"; "Jennifer"; "Jennifer"; "John"; "Laura"; "Laura"; "Jennifer"; "Jennifer"; "Laura"; "John"; "John"; "Jennifer"; "Jennifer"; "John"; "John"; "Jennifer"; "Jennifer"; "John"; "Jennifer"; "John"; "Jennifer"; "John"}

and returns

{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; 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))

becomes

({40544; 40548; 40550; 40552; 40557; 40561; 40561; 40565; 40569; 40573; 40576; 40579; 40582; 40585; 40588; 40592; 40593; 40594; 40594; 40597; 40600; 40601}<=DATE(2011, 1, 31))

becomes

({40544; 40548; 40550; 40552; 40557; 40561; 40561; 40565; 40569; 40573; 40576; 40579; 40582; 40585; 40588; 40592; 40593; 40594; 40594; 40597; 40600; 40601}<=40574)

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 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)))

becomes

1/{2; 1; 2; 2; 0; 2; 2; 2; 2; 2; 0; 2; 1; 2; 0; 0; 0; 2; 2; 2; 2; 2}

and returns

{0.5; 1; 0.5; 0.5; #DIV/0!; 0.5; 0.5; 0.5; 0.5; 0.5; #DIV/0!; 0.5; 1; 0.5; #DIV/0!; #DIV/0!; #DIV/0!; 0.5; 0.5; 0.5; 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)

becomes

IF({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)

becomes

IF({1;1;1;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, 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),

IF({1;1;1;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)

becomes

IF({1;1;1;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0}, {0.5; 1; 0.5; 0.5; #DIV/0!; 0.5; 0.5; 0.5; 0.5; 0.5; #DIV/0!; 0.5; 1; 0.5; #DIV/0!; #DIV/0!; #DIV/0!; 0.5; 0.5; 0.5; 0.5; 0.5}, 0)

and returns

{0.5; 1; 0.5; 0; 0; 0; 0.5; 0.5; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 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))

becomes

SUM({0.5; 1; 0.5; 0; 0; 0; 0.5; 0.5; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 3 in cell C3. There are three unique distinct products based on two conditions.

Recommended article:

The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]

*Example 2,*

How many unique distinct products did Jennifer sell in January **and** in region South?

**Array formula in D3:**

Recommended article:

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 […]

Count unique distinct values based on a condition

*Example 3,*

How many unique distinct products was sold in the south **or** in January?

Array formula:

Recommended article:

Count dates inside a date range

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)) + […]

Count dates inside a date range

Count unique distinct values in a filtered Excel defined Table

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

Count unique distinct values in a filtered Excel defined Table

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 based on a condition

The following article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image […]

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 […]

How to count unique distinct values based on a date

The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]

### 100 Responses to “Count unique distinct values that meet multiple criteria”

### Leave a Reply to Stephen

### 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.

**Contact Oscar**

You can contact me through this contact form

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))) Download 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!