counting unique values incorrectly (by +1)

[avengedJC]

Hi,

I have an array formula in a cell to count the number of unique dates based on criteria in another cell:

=SUM(IF(FREQUENCY(IF(DATA!$H:$H="JC",DATA!$I:$I),DATA!$I:$I),1))

So if "JC" is in cells in column H, then the number of unique values (in this case dates) will be counted in column I.

The only trouble I have is that for some reason, the result this gives me is 6, when there are actually only 5 unique dates. It is counting one extra for some reason.

So I came to the conclusion that there must be one extra date that I have missed. But using the filter function only lists 5 unique dates (+blanks) AND I have counted them myself and there are only 5. This lead me to think that it must be counting the blanks as a unique value as well. However, I have the exact same function to count dates with "CW" in column H and there are also blanks in the date column yet the formula doesn't seem to count the blanks and the value it gives me is correct.

Does anyone have any idea why this might be happening?

Thanks for your response in advance.

 

[Marcelo Branco]

My data sample (dates as dd/mm/yyyy)

Sheet DATA

	H​	I​
1​	Value​	Dates​
2​	JC​	01/01/2018​
3​	JC​	01/01/2018​
4​	AC​
5​	JC​	03/01/2018​
6​	AC​
7​	JC​
8​	AC​	05/01/2018​
9​	XX​	06/01/2018​
10​	JC​
11​	XY​
12​	JC​	02/01/2018​
13​
14​	JC​	03/01/2018​
15​	JC​

Formula
=SUM(IF(FREQUENCY(IF(DATA!H2:H20="JC",DATA!I2:I20),DATA!I2:I20),1))
returns 3 as expected

M.

 

[Marcelo Branco]

Hello Marcelo,

If I put "JC" in H1 and H3 and H5 with all other cells blank the formula returns 1 for me because FREQUENCY function returns {3;0}, the 3 representing the three rows with "JC"

I'm using Excel 2010

Hi Barry

Yes, this i can replicate (weird), but with just one cell with a date the formula works perfectly.

	H​	I​
1​	Value​	Dates​
2​	JC​
3​	JC​
4​	AC​
5​	JC​	01/01/2018​
6​	AC​
7​	JC​
8​	AC​
9​	XX​
10​	JC​
11​	XY​
12​	JC​
13​
14​	JC​
15​	JC​
16​

For me the formula returned 1 as expected (Excel 2010)

M.

 

[barry houdini]

Hello Marcelo,

I think you get the right result (3) in your first example, but that's just because of the nature of the bins in FREQUENCY function, in your example the zeroes and the smallest counted date, 01/01/2018 fall in the same bin so there's no extra count for zero.....but if the lowest counted date isn't the lowest date overall then you'll get an erroneous result - try changing I8 to 5/1/2017

 

[Marcelo Branco]

Hello Marcelo,

I think you get the right result (3) in your first example, but that's just because of the nature of the bins in FREQUENCY function, in your example the zeroes and the smallest counted date, 01/01/2018 fall in the same bin so there's no extra count for zero.....but if the lowest counted date isn't the lowest date overall then you'll get an erroneous result - try changing I8 to 5/1/2017

OMG you're right!!!
I can see now what is going on: it's obvious, a blank is taken as 0.
That's why i said "I'm curious" - i could feel i was missing something (it seems it's not my day)

Thank you for clarifying this issue.

M.