Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

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

  1. #1
    Guest

    Default

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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Guest

    Default

    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.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,599
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  5. #5
    Guest

    Default

    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)



    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.

  6. #6
    Guest

    Default

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

    1. does the exponential match create a serial number?

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

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,599
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    > 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.

  8. #8
    Guest

    Default

    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...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •