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

A	B	C	D
3			7/1/2016
17
30			7/1/2016
32			7/1/2016
34			13/1/2016
46			11/1/2016
48			11/1/2016
48			11/1/2016
61			15/1/2016
62
62
63			15/1/2016
88			16/1/2016

<tbody>
</tbody>

 

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