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.

 

[Truiz]

Assuming your data set has headers and your formula includes the whole column that is most likely why you are getting the extra value

See here:

	A	B	C	D
1	Costumer ID	Order#	Status	Date
2	123	54	Complete	1/1/2018
3	234	88785	submitted	1/2/2018
4	345	845	Somehting	1/1/2018
5	234	8	Somehting	1/3/2018
6	123	54686	Somehting	1/1/2018

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

There are 3 unique dates but also the header i unique thus the result should be 4

 

[NdNoviceHlp]

maybe..

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

HTH. Dave

 

[avengedJC]

Assuming your data set has headers and your formula includes the whole column that is most likely why you are getting the extra value

See here:

	A	B	C	D
1	Costumer ID	Order#	Status	Date
2	123	54	Complete	1/1/2018
3	234	88785	submitted	1/2/2018
4	345	845	Somehting	1/1/2018
5	234	8	Somehting	1/3/2018
6	123	54686	Somehting	1/1/2018

<tbody>
</tbody>

​

There are 3 unique dates but also the header i unique thus the result should be 4

I understand and could see this would be possible. However as I described in my first post, I have the exact same formula and it is giving correct values for "CW" in column G. i.e. it doesn't seem to count the header.

Could you possibly suggest how I could edit my formula so that it wouldn't count the header though? I could try it and then see if it works?

Thanks!

 

[barry houdini]

It's only going to count the header if 1) the H1 value = "JC" and 2) there's a number (or blank) in J1

.....but it will also count any blanks in column I (as zero) if column H = "JC"

Try excluding blanks like this:

=SUM(IF(FREQUENCY(IF(Data!$H:$H="JC",IF(Data!$I:$I<>"",Data!$I:$I)),Data!$I:$I),1))

[although I'd advise you to also restrict the ranges, rather than using whole columns]

 

[avengedJC]

maybe..

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

HTH. Dave

Hmmm... This just gives me a value of "0"!

 

[avengedJC]

Yes it will also count any blanks in column I (as zero) if column H = "JC"

Try excluding blanks like this:

=SUM(IF(FREQUENCY(IF(Data!$H:$H="JC",IF(Data!$I:$I<>"",Data!$I:$I)),Data!$I:$I),1))

[although I'd advise you to also restrict the ranges, rather than using whole columns]

THANKS! This worked! I see where I was going wrong now too.

May I ask why you advise to restrict the ranges? I could obviously do this but the database will potentially be 5000+ rows by the end of the year. I just figured easier to do the whole column lol.

 

[barry houdini]

May I ask why you advise to restrict the ranges?

When you use SUMIFS or COUNTIF with the whole column Excel is "clever" enough to only use the "used range", but with array formulas like these if you use the whole column it's calculating across more than 1 million rows, so better to restrict to a smaller range, e.g. 10,000 rows to speed up processing

 

[avengedJC]

Ah, okay. I did think of this but didn't want to have to type H1:H10000 a load of times lol. The document seems to be performing okay at the moment but I will keep that in mind if it starts to struggle. Thank again .

 

[Marcelo Branco]

I'm curious...
I cannot replicate this behavior even with some rows where Hx="JC" and Ix blank (x = row number).
I got a wrong count only with a Custom Format in column I that displays 0 as blank.

M.