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>

 

[ww4612]

3	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	7/1/2016	=SUM(C3)
17	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))
30	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	7/1/2016
32	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	7/1/2016
34	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	13/1/2016
46	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	11/1/2016
48	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	11/1/2016
48	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	11/1/2016
61	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	15/1/2016
62	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))
62	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))
63	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	15/1/2016
88	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))	16/1/2016
3	=COUNTIF(C1,RC1)	=--AND(RC2>0,NOT(RC4=""))

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[damak]

Sorry, i dont really undestand how that works

I need a formula to give the results in one cell, for example in cell E1 the result of unique values in column A that are blank in column D... and in cell E2 the result of unique values in column A that are blank in column D

 

[damak]

Sorry, i dont really undestand how that works

I need a formula to give the results in one cell, for example in cell E1 the result of unique values in column A that are blank in column D... and in cell E2 the result of unique values in column A that are blank in column D

correction: I need a formula to give the results in one cell, for example in cell E1 the result of unique values in column A that are blank in column D... and in cell E2 the result of unique values in column A that are not blank in column D

 

[ww4612]

3	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	7/1/2016	=SUM(C3)
17	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")		=COUNTIF(C1,">0")-R1C5
30	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	7/1/2016
32	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	7/1/2016
34	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	13/1/2016
46	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	11/1/2016
48	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	11/1/2016
48	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	11/1/2016
61	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	15/1/2016
62	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")
62	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")
63	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	15/1/2016
88	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")	16/1/2016
3	=COUNTIF(C1,RC1)	=--AND(RC2>0,RC4="")

<tbody>
</tbody>

this is what you want to achieve. just paste it to you excel sheet, you shall see how it work

 

[Aladin Akyurek]

1. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-ISNUMBER($D$1:$D$13),$A$1:$A$13),$A$1:$A$13),1))

2. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($D$1:$D$13),$A$1:$A$13),$A$1:$A$13),1))

 

[damak]

Sorry, it's not working

Can't threre be a fromula to paste in one single cell and give the result?

Do i need to do all that for every new record?

Please note that in my sheet there are no empty columns between the columns that contain numbers and dates

 

[damak]

1. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-ISNUMBER($D$1:$D$13),$A$1:$A$13),$A$1:$A$13),1))

2. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER($D$1:$D$13),$A$1:$A$13),$A$1:$A$13),1))

It gives me an error message

 

[Aladin Akyurek]

It gives me an error message

You should always say what message that is, but I presume it's #VALUE!. That would mean that you did not apply control+shift+enter correctly...

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. When done properly, Excel itself will put a pair of { and } around the formula.

 

[damak]

You should always say what message that is, but I presume it's #VALUE!. That would mean that you did not apply control+shift+enter correctly...

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. When done properly, Excel itself will put a pair of { and } around the formula.

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