Unique values count

D

damak

New Member
Joined
Mar 3, 2016
Messages
10
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]

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
[TABLE="width: 636"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]7/1/2016[/TD]
[TD]=SUM(C3)[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]7/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]7/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]13/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]15/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]15/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD]16/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,NOT(RC4=""))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
damak said:
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
Click to expand...

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
 
Upvote 0
[TABLE="width: 718"]
<tbody>[TR]
[TD]3[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]7/1/2016[/TD]
[TD]=SUM(C3)[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD][/TD]
[TD]=COUNTIF(C1,">0")-R1C5[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]7/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]7/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]13/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]11/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]15/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]15/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD]16/1/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=COUNTIF(C1,RC1)[/TD]
[TD]=--AND(RC2>0,RC4="")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

this is what you want to achieve. just paste it to you excel sheet, you shall see how it work
 
Last edited:
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
Aladin Akyurek said:
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))
Click to expand...

It gives me an error message
 
Upvote 0
damak said:
It gives me an error message
Click to expand...

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.
 
Upvote 0
Aladin Akyurek said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
modify excel formula
Replies
5
Views
367
Peter_SSs
Peter_SSs
H
  • Solved
How to make excel count based on time data?
Replies
3
Views
488
Fluff
Fluff
ibmy
  • Solved
Count number of occurence of value with condition
2
Replies
10
Views
244
Alex Blakenburg
A
T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
252
myall_blues
myall_blues
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
250
djclements
djclements

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top