Unique values count

[damak]

Hi everyone

I need help for a formula to count unique values in column A that are blank in column D ...and also to count unique values in column A that are not blank in column D.

I use Excel 2003


[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]7/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]13/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]46[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]61[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]62[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]62[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]63[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]15/1/2016[/TD]
[/TR]
[TR]
[TD="align: center"]88[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]16/1/2016[/TD]
[/TR]
</tbody>[/TABLE]

 

[damak]

I don't have English Windows, so the translation of the error message is: "The formula you typed contains an error"

I applied ctrl+shift+enter as you describe (and i also paid attention not to miss a bracket) but i always get the same error message

 

[Aladin Akyurek]

I don't have English Windows, so the translation of the error message is: "The formula you typed contains an error"

I applied ctrl+shift+enter as you describe (and i also paid attention not to miss a bracket) but i always get the same error message

See: https://dl.dropboxusercontent.com/u/65698317/damak Unique values count.xlsx

 

[damak]

See: https://dl.dropboxusercontent.com/u/65698317/damak Unique values count.xlsx

I see it. Thank you

But i think that the results in cells G1 and G2 are not correct

The result for the unique values in column A that are blank in column D should be 2 ...and the result for the unique values in column A that are not blank in column D should be 9

 

[Aladin Akyurek]

I see it. Thank you

But i think that the results in cells G1 and G2 are not correct

The result for the unique values in column A that are blank in column D should be 2 ...and the result for the unique values in column A that are not blank in column D should be 9

But they are. I did not correct your "date" entries, that's the issue. Please download the file again to see that...

 

[damak]

But they are. I did not correct your "date" entries, that's the issue. Please download the file again to see that...

Yes. Everything is ok

Thank you very much

 

[Aladin Akyurek]

Yes. Everything is ok

Thank you very much

You are welcome.

 

[damak]

You are welcome.

Just one more thing

When i expand the criteria range, the result in cell G1 changes from the correct 2 to 3, without adding any new records. The result in cell G2 remains 9, which is correct.

Brackets and $ symbols are all in place.

Any idea why that happens?

 

[Aladin Akyurek]

Just one more thing

When i expand the criteria range, the result in cell G1 changes from the correct 2 to 3, without adding any new records. The result in cell G2 remains 9, which is correct.

Brackets and $ symbols are all in place.

Any idea why that happens?

You need to adjust the range in the formula and apply control+shift+enter anew. A possible option is to convert the data area into a table by means of the Insert | Table option. When done so, the records get automatically added.