a yearly list that i want to find how many items in a month

[Guest]

i have this whole table of calls taken, and when. i want to count how many are within a a month.

is there an easier way than using a =countif(A:A>[serial number] and A:A<[serial number])?

(where [serial number] is the number for a date?)

 

[Mark W.]

I'd use a PivotTable. If all of the rows in your Date column contain legimate date values (no blanks or text) you can group that field into months.

 

[Guest]

nope, not all of it are legitimate dates. what happened is that people put in their call log manually, and depending on who did it, they have all sorts of weird date formats (ie. 3-6, 3/6/2001, and some of them are just plain screwed up, ie 36/2001) some of them in fact, didn't even put a date in.

i'd like to be able to count all the legitimate dates, display it as per month, and maybe have a count for the illegitimate ones.

 

[Aladin Akyurek]

On 2002-03-07 08:53, Anonymous wrote:
nope, not all of it are legitimate dates. what happened is that people put in their call log manually, and depending on who did it, they have all sorts of weird date formats (ie. 3-6, 3/6/2001, and some of them are just plain screwed up, ie 36/2001) some of them in fact, didn't even put a date in.

i'd like to be able to count all the legitimate dates, display it as per month, and maybe have a count for the illegitimate ones.

Assuming that the dates start at row 2:

In D1 enter:

=MATCH(9.99999999999999E+307,A:A)

Create a list of month numbers from D2 on, that is, 1,2,3,...etc.

In E2 array-enter:

=SUM(IF(ISNUMBER(OFFSET($A$2,0,0,$D$1,1)),MONTH(OFFSET($A$2,0,0,$D$1,1))=$D2)+0)

Copy down this till the row of the month 12.

This should give you a count of legal dates per month.

In F2 enter:

=COUNTA(A:A)-SUM(E:E)

should give you the total number of illegal dates.

 

[Guest]

please go over this slooowly. i'm really new at all this excel stuff.
this is what you wrote:

Assuming that the dates start at row 2:
In D1 enter:
=MATCH(9.99999999999999E+307,A:A)

<huh? what does this do? is this a method to find a serial number?>

Create a list of month numbers from D2 on, that is, 1,2,3,...etc.
<list of month?>

In E2 array-enter:
=SUM(IF(ISNUMBER(OFFSET($A$2,0,0,$D$1,1)),MONTH(OFFSET($A$2,0,0,$D$1,1))=$D2)+0)

Copy down this till the row of the month 12.

This should give you a count of legal dates per month.
<does this do it per year too? or will the 2000 get mixed up with the 2001>

In F2 enter:
=COUNTA(A:A)-SUM(E:E)
should give you the total number of illegal dates.

 

[Guest]

crud.. my comments didn't show...

1. does the exponential match create a serial number?

2. i'm a bit lost on the references.

 

[Aladin Akyurek]

> 1. does the exponential match create a serial number?

I assumed that you meant by a serial number the internal representation of a date by Excel. The formula will recognize correctly entered dates and skip incorrectly entered "dates".

> i'm a bit lost on the references.

How dou you mean? You need to adjust the formula to fit your layout (or the layout to fit the formula). Otherwise give the exact range of your data data.

On 2002-03-07 09:48, Anonymous wrote:
crud.. my comments didn't show...

1. does the exponential match create a serial number?

2. i'm a bit lost on the references.

 

[Guest]

A:A is the date range

soooo... from what i gather, you convert the dates to 1s, 2s, 3s, to 12s then sort by these numbers?

i have about 1000+ rows, so this kinda sucks...