sumproduct or countif

[Guest]

hi,
i am trying to count the number of times a date repeats. eg:
4/1/07
4/1/07
4/2/07
4/2/07
4/3/07
..
..
..
4/30/07
4/30/07
ect...
for the whole month
data corralates with number of visits per day.

i have created a helper colum B with number 1-31, and used the following
=SUMPRODUCT(--$A$2:$A$1000>=$B2),--($A$2:$A$1000<$B$3))
where A is data, B is helper
returns 0 in all. formated to gen number. have tried several diff formats.

have also tried
=COUNT(A2:A1000)-SUMPRODUCT((B2:B2000<>")/COUNTIF(B2:B1000,B2:B1000+(B2:B1000="")))

have also tried
=SUMIF(A1:A1000,"<"&DATE(4/1/07),B1:B31-SUMIF(A1:A1000,+<"&DATE(4,1,07),B1:B31)
It initially returned 0 then next time thru returned N/A


i have tried xldynamic.com
i'm sure there is an easirer way to do this. i'm terrible with formulas, but
i'm chugging at it and i think i'm over my head...
thanks in advance

 

[Guest]

Consider using a Pivot Table. It will produce a nice table that lists each
date uniquely and the number of time the date occurs in your table. For
example:

tades
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/15/2007
6/15/2007
6/15/2007
6/15/2007
6/15/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
can produce a table like:


Count of tades
tades Total
6/15/2007 5
6/16/2007 9
6/17/2007 22
(blank)
Grand Total 36


see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Sandy Mann]

You will either have to change the 1- 31 numbers in Column B into dates:

4/1/07
4/2/07
4/3/07 etc. and then use:

=COUNTIF($A$1:$A$31,B1)
in C1 and drag down using the fill handle to C31

or with 1 - 31 in Column B use:

=SUM(--(DAY(A1:A24)=B2))
entered as an array formula by pressing and holding Ctr + Shift while you
press Enter. If you do it right then XL will surround the formula with
curly braces:

={SUM(--(DAY($A$1:$A$31)=B1))}

or use:
=SUMPRODUCT(--(DAY($A$1:$A$31)=B1))
normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

thanks g 's
-- will give it a try.

dr350x

Consider using a Pivot Table. It will produce a nice table that lists each
date uniquely and the number of time the date occurs in your table. For
example:

tades
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/16/2007
6/15/2007
6/15/2007
6/15/2007
6/15/2007
6/15/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
6/17/2007
can produce a table like:


Count of tades
tades Total
6/15/2007 5
6/16/2007 9
6/17/2007 22
(blank)
Grand Total 36


see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

ausome, both worked great!!
thanks to both for the help
--
dr350x

You will either have to change the 1- 31 numbers in Column B into dates:

4/1/07
4/2/07
4/3/07 etc. and then use:

=COUNTIF($A$1:$A$31,B1)
in C1 and drag down using the fill handle to C31

or with 1 - 31 in Column B use:

=SUM(--(DAY(A1:A24)=B2))
entered as an array formula by pressing and holding Ctr + Shift while you
press Enter. If you do it right then XL will surround the formula with
curly braces:

={SUM(--(DAY($A$1:$A$31)=B1))}

or use:
=SUMPRODUCT(--(DAY($A$1:$A$31)=B1))
normally entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

You're very welcome. Thanks for the feedback

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

ausome, both worked great!!
thanks to both for the help

 

[Dave Peterson]

I'd also put the real dates in the helper column, but you could use this, too:

=countif($a$2:$a$1000,date(2007,4,b2))

 

[Bernd P]

Hello,

Another approach:

http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd