Thread
:
sumproduct or countif
View Single Post
#
6
Posted to microsoft.public.excel.misc
Sandy Mann
external usenet poster
Posts: 2,345
sumproduct or countif
You're very welcome. Thanks for the feedback
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
with @tiscali.co.uk
"dr350x" wrote in message
...
ausome, both worked great!!
thanks to both for the help
--
dr350x
"Sandy Mann" wrote:
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
with @tiscali.co.uk
"dr350x" wrote in message
...
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
--
dr350x
Reply With Quote
Sandy Mann
View Public Profile
Find all posts by Sandy Mann