Counting Selected Dates

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

In a separate cell I want to count each unique date in the following
column of dates starting with cell x700. The answer would be three.

I could use a little help with this, thanks.

1/30/10
1/30/10
1/30/10
1/30/10
1/30/10
1/30/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10
 
Hi Jim

One way would be to use a helper column.
In cell Y700 enter
=IF(X700="","",IF(COUNTIF($X$700:X700,X700)>1,"",1))
Copy down as far as required and then the result is
=SUM(Y700:Y65536)

--
Regards
Roger Govier

JimS said:
In a separate cell I want to count each unique date in the following
column of dates starting with cell x700. The answer would be three.

I could use a little help with this, thanks.

1/30/10
1/30/10
1/30/10
1/30/10
1/30/10
1/30/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
1/31/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10
2/1/10


__________ Information from ESET Smart Security, version of virus
signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4826 (20100202) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Hello Jim,

If you have no blanks then the following formula entered with CTRL SHFT
ENTER will give you: 3
(dates:a1:a10)
=sum(1/countif(a1:a10,a1:a10))

Good luck

Gabor
 
Thanks, But what if I have blanks at the end of the column. In other
words, I want it to update daily. If I have data in A1:A10, but
A11:A100 is blank is there a way I can alter the formula to unclude
A1:A100 even though most of them are blank?

=sum(1/countif(a1:a100,a1:a100))

Thanks
 
Hello Jim,

If there are blanks then the formula is longer.
Here it goes:

=sum(countif(a1:a100,a1:a100)/if(not(countif(a1:a100,a1:a100)),1,countif(a1:a100,a1:a100))^2)
CTRL SHFT ENTER


Best wishes

Gabor Sebo
 
Works perfect, thanks much!

Hello Jim,

If there are blanks then the formula is longer.
Here it goes:

=sum(countif(a1:a100,a1:a100)/if(not(countif(a1:a100,a1:a100)),1,countif(a1:a100,a1:a100))^2)
CTRL SHFT ENTER


Best wishes

Gabor Sebo
Click to expand...
 
Back
Top