+ Reply to Thread
Results 1 to 4 of 4

Sumproduct. count how many time a month appears (in column filled by date or text). #Value

  1. 04-10-2013, 08:23 AM #1
    adriendriver
    adriendriver is offline
    Registered User
    Join Date
    04-10-2013
    Location
    Bali, indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Sumproduct. count how many time a month appears (in column filled by date or text). #Value

    Hello everyone,

    I hope my question has not been answered yet, I search for hours...
    My column 'M' is filled by date (dd/mm/yyyy) but also by text ("hold").
    basically, I want to know how many time a dedicated month (let's November, so month=11) appears in this column.

    My formula is
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    my column 'M' looks like this:
    28/11/2012
    Hold
    28/11/2012
    29/11/2012
    29/11/2012
    30/11/2012
    1/12/2012
    5/12/2012
    6/12/2012

    ==> Without the "Hold", my formula indicate the result of "5" (november appears 5 times) so it is perfect.
    ==> With the "Hold", my formula gives "#value"

    Any idea?
    Reply With QuoteRegister To Reply
  2. 04-10-2013, 08:38 AM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sumproduct. count how many time a month appears (in column filled by date or text). #V

    This "array formula" would do it

    =COUNT(IF(ISNUMBER(M7:M75);IF(MONTH(M7:M75)=11;1)))

    confirmed with CTRL+SHIFT+ENTER

    or try using TEXT function

    =SUMPRODUCT((TEXT(M7:M75,"mmm")="Nov")+0)

    You may have to change "mmm" and "Nov" to match regional settings with that second formula
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 04-10-2013, 08:40 AM #3
    adriendriver
    adriendriver is offline
    Registered User
    Join Date
    04-10-2013
    Location
    Bali, indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Sumproduct. count how many time a month appears (in column filled by date or text). #V

    That was fast!
    thanks a lot daddylonglegs!
    I did try to use an array formula but obviously done something wrong.

    Anyway, it works now. thx again!
    Reply With QuoteRegister To Reply
  4. 04-10-2013, 08:43 AM #4
    dilipandey
    dilipandey is offline
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sumproduct. count how many time a month appears (in column filled by date or text). #V

    Hi adriend,

    welcome to the forum.

    try using below formula which need to be entered using ctrl shift enter key combination:-

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1