| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct or countif
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct or countif
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 -- Gary''s Student - gsnu200730 "dr350x" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct or countif
thanks g 's
-- will give it a try. dr350x "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200730 "dr350x" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct or countif
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct or countif
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)) dr350x wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct or countif
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct and Countif together | Excel Discussion (Misc queries) | |||
| Sumproduct and Countif | Excel Discussion (Misc queries) | |||
| sumproduct vs. countif | Excel Discussion (Misc queries) | |||
| Countif or Sumproduct | Excel Discussion (Misc queries) | |||
| SumProduct or CountIf | Excel Worksheet Functions | |||
Linear Mode
