1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Counting Months only in Excel

Discussion in 'Business Applications' started by PincivMa, Oct 17, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. PincivMa

    PincivMa Thread Starter

    Joined:
    Mar 13, 2004
    Messages:
    378
    Hi again

    This time I've encountered another problem with dates. Below are a series of dates in one column formatted mmm-yy. I have many of these dates ranging from Jan-05 to Dec-05 and a few in Jan-06. I want to have a count of how many Jan-05 I have, how may Feb-05 I have etc. I want a number count. for example I have 2 May-05 and 8 Sep-05. The countif formula does not work since it requires the full date, i.e. May 12, 2005 etc. But then it only gives me 1 count only, since the other May-05 has a different date. All I want is to count the months only. Any idea how to do this?? Do you have to write a macro to accomplish this task??

    Mario


    May-05
    May-05
    Jun-05
    Jun-05
    Jul-05
    Jul-05
    Aug-05
    Aug-05
    Sep-05
    Sep-05
    Sep-05
    Sep-05
    Sep-05
    Sep-05
    Sep-05
    Sep-05
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    First Name:
    Anne
  3. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hey Dreamboat,

    Have a look at an alternative solution, attached, using an Array Formua!
    I took a challenge against the Office Article, he he.

    PincivMa,
    If you want to do it this way, note that the formula in col D is an ARRAY FORMULA, you key it as shown without the surrounding { } then instead of ENTER, press CTRL+SHIFT+ENTER at the same time.

    Col C was entered as 1/1, 1/2, 1/3, 1/4...... then formatted as MMM for display.
    (I'm in the UK so those were 1-Jan, 1-Feb, 1-Mar....)

    lol
    Hew
     

    Attached Files:

  4. Dreambringer

    Dreambringer

    Joined:
    Jan 19, 2005
    Messages:
    1,351
    Couldnt you just add another Column and enter 1 for each cell, hide the cell and then create a pivot chart?

    Its nothing fancy like these guys but it gets the job done... check my sample.
     

    Attached Files:

  5. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    Try,

    =SUMPRODUCT(--(DATE(YEAR($A$1:$A$10),MONTH($A$1:$A$10),1)=B1))

    Where B1 houses a date which month/year you're trying to count.
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    First Name:
    Anne
    LOL. My Office Articles aren't really THE answer, but they're intended to point someone in the right direction.
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    First Name:
    Anne

    Brian: Someday you're going to have to teach me about those -- formulas.
     
  8. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    You and Scott come visit me! :D
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    First Name:
    Anne
    I wish!!
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Counting Months Excel
  1. sia123
    Replies:
    5
    Views:
    423
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/408681

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice