+ Reply to Thread
Results 1 to 8 of 8

Counting Months year to date.

  1. #1
    Registered User
    Join Date
    05-11-2010
    Location
    Kennewick, Washington
    MS-Off Ver
    Excel 2007
    Posts
    19

    Counting Months year to date.

    I have a list of events that take place in two years, this and last. I need a formula to count what month and year thing take place.

    For example:

    If I have 5 things happen in January, 3 last year and 2 this year. And I have 3 things happen in April, 1 last year and 2 this year.

    This is displayed in column A.

    [A]
    Event Date
    Jan-09
    Apr-09
    Jan-09
    Apr-10
    Jan-09
    Jan-10
    Apr-10
    Jan-10

    In column B and C I would like it to display.

    [B]--------[C]
    [Jan-09]-[3]
    [Apr-09]-[1]
    [Jan-10]-[2]
    [Apr-10]-[2]

    This is the formula I have tried to use.

    =SUM(IF(MONTH($A$2:$A$100)=MONTH(B4),1))
    push: Ctrl+Shift+Enter

    I changed it like this:

    =SUM(IF(AND(MONTH($A$2:$A$100)=MONTH(B4),YEAR($A$2:$A$100)=YEAR(B4)),1))
    push: Ctrl+Shift+Enter

    Thank you, so much.

    P.S. Also if some could explain what pushing Ctrl+Shift+Enter does or where i can find it that would be great. But not needed.
    Last edited by WKM; 05-18-2010 at 04:40 PM.

  2. #2
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Counting Months year to date.

    =SUMPRODUCT(--(MONTH(a2:a100)=MONTH(b4)))

    just entered normally, will count for same month any year and :-

    =SUMPRODUCT(--((a2:a100-DAY(a2:a100))=(b4-DAY(b4))))

    Will check for month and year the same!

    Again not CSE entered

    CTRL-SHIFT-ENTER (CSE) designates the formula an array formula allowing it to work with groups of cells...


    [Alternatively =SUMPRODUCT(--(TEXT(a2:a100,"MM")=TEXT(b4,"MM")))
    OR
    =SUMPRODUCT(--(TEXT(a2:a100,"YYMM")=TEXT(b4,"YYMM"))) ]
    Last edited by squiggler47; 05-14-2010 at 02:27 PM. Reason: Alternative
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  3. #3
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Counting Months year to date.

    Have tried PIVOT TABLES

    It's easy!! see sample book
    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  4. #4
    Registered User
    Join Date
    05-11-2010
    Location
    Kennewick, Washington
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Counting Months year to date.

    The first suggestion did work great, but now i also have to designate it by the site that it took place as well. I have tried this:

    =SUMPRODUCT(--(AND(TEXT('Sheet1'!$F$1:$F$200,"YYMM")=TEXT(C14,"YYMM"),'Sheet1'!E1:E36=A2)))

    Where A2 is where the event took place for example: California, Idaho, Wyoming, Nevada, Utah, ect.

    So I would like it to count and separate it by Month/Year and by the site it took place.

    Thank you.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Counting Months year to date.

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

  6. #6
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Counting Months year to date.

    =SUMPRODUCT(--((TEXT('Sheet1'!$F$1:$F$200,"YYMM")=TEXT(C14,"YYMM")*('Sheet1'!E1:E36=A2))))

    Multiply in a sumproduct formula has the same result as AND

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Counting Months year to date.

    If you're using multiplication between the Arrays there is zero requirement for double unary - you're needlessly duplicating the coercion.

    So

    =SUMPRODUCT((TEXT('Sheet1'!$F$1:$F$200,"YYYYMM")=TEXT(C14,"YYYYMM"))*('Sheet1'!E1:E36=A2))

    EDIT: are you sure the E1:E36 reference is correct ? Should it not be E1:E200 ?

    When using String conversion I would also suggest you use YYYYMM rather than YYMM this will account for blanks which could otherwise be interpreted as 0001 (ie 1900 or 2000)
    Last edited by DonkeyOte; 05-18-2010 at 03:04 AM. Reason: typo - parenthesis

  8. #8
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Counting Months year to date.

    True donkeyote, I forgot to delete them!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1