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

G

Guest

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?)
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
crud.. my comments didn't show...

1. does the exponential match create a serial number?

2. i'm a bit lost on the references.
 
Upvote 0
> 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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top