Counting dates

JRRT · May 4th, 2002, 08:53 PM

I have the folowing in cells e1:e18 the formula I need will be in cell e20. What I would like to do is count the dates but I have some dates listed more than once. What formula can I use so that it will only count the date once even though it may be listed twice
For example when it finds the date April 26 it only counts it once even though it is listed twice? Thanks for your help

Date Planted
April 26,29
April 26,29
April 30,May 1
April 30
April 18,19,20
April 19,20
April 23,24
April 23
April 20,22
April 20
April 18
April 18
April 24,25
April 17,18
May 1,2
May 1,2
May 1,2

eliW · May 4th, 2002, 11:21 PM

JRRT,
It was discussed many times before. Just make a search on "count" and "unique" and you'll find lot of stuff. I tried one and it works:

Put in E20 the formula:

=SUMPRODUCT(1/COUNTIF(E1:E18,E1:E18))

and you'll have the unique count
Eli

(All rights reserved to the original posters)

[ This Message was edited by: eliW on 2002-05-04 22:40 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 4th, 2002, 08:53 PM	#1
JRRT New Member Join Date: Mar 2002 Posts: 21	I have the folowing in cells e1:e18 the formula I need will be in cell e20. What I would like to do is count the dates but I have some dates listed more than once. What formula can I use so that it will only count the date once even though it may be listed twice For example when it finds the date April 26 it only counts it once even though it is listed twice? Thanks for your help Date Planted April 26,29 April 26,29 April 30,May 1 April 30 April 18,19,20 April 19,20 April 23,24 April 23 April 20,22 April 20 April 18 April 18 April 24,25 April 17,18 May 1,2 May 1,2 May 1,2

May 4th, 2002, 11:21 PM	#2
eliW Board Regular Join Date: Mar 2002 Posts: 1,805	JRRT, It was discussed many times before. Just make a search on "count" and "unique" and you'll find lot of stuff. I tried one and it works: Put in E20 the formula: =SUMPRODUCT(1/COUNTIF(E1:E18,E1:E18)) and you'll have the unique count Eli (All rights reserved to the original posters) [ This Message was edited by: eliW on 2002-05-04 22:40 ]