+ Reply to Thread
Results 1 to 7 of 7

How to count entries monthwise for current year formula

Hybrid View

  1. 05-26-2014, 03:31 AM #1
    KK1234
    KK1234 is offline
    Forum Contributor
    Join Date
    02-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    677

    How to count entries monthwise for current year formula

    Hi all,
    How to count monthwise dates for current year??

    This formula will count monthwise but not current year:

    Example is for say month of June ( Dates are in column A)
    =SUMPRODUCT(--(MONTH(sheet2!A2:A761)=6))
    Any suggestions please

    Kind regards
    Reply With QuoteRegister To Reply
  2. 05-26-2014, 03:39 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to count entries monthwise for current year formula

    Ηι

    Try

    Formula: copy to clipboard 
    =SUMPRODUCT((YEAR(A2:A761)=YEAR(TODAY())*(MONTH(A2:A761)=6))*(A2:A761<>""))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 05-26-2014, 03:41 AM #3
    KK1234
    KK1234 is offline
    Forum Contributor
    Join Date
    02-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    677

    Re: How to count entries monthwise for current year formula

    I Tried but it will not work
    =SUMPRODUCT(--(MONTH(sheet2!A2:A761)=2),(YEAR(sheet2!A2:A761)=YEAR(TODAY())))
    Reply With QuoteRegister To Reply
  4. 05-26-2014, 03:46 AM #4
    KK1234
    KK1234 is offline
    Forum Contributor
    Join Date
    02-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    677

    Re: How to count entries monthwise for current year formula

    Hi Fotis1991,
    Thanks . Before I could see your reply I did responded with previous.
    I have tried your suggestion and it has worked wonderfully.
    Great as always is your help.
    Thanks
    Kind regards
    Reply With QuoteRegister To Reply
  5. 05-26-2014, 10:51 AM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count entries monthwise for current year formula

    Here's another one.

    Data Range
    A
    B
    C
    D
    1
    Date
    ------
    Month
    Count
    2
    1/5/2013
    Jan
    1
    3
    1/9/2013
    Feb
    0
    4
    5/3/2013
    Mar
    1
    5
    9/3/2013
    Apr
    0
    6
    9/21/2013
    May
    0
    7
    9/26/2013
    Jun
    0
    8
    10/6/2013
    Jul
    0
    9
    1/30/2014
    Aug
    1
    10
    3/18/2014
    Sep
    1
    11
    8/31/2014
    Oct
    3
    12
    9/22/2014
    Nov
    1
    13
    10/6/2014
    Dec
    0
    14
    10/15/2014
    15
    10/24/2014
    16
    11/17/2014
    17
    4/1/2015
    18
    7/5/2015
    19
    9/27/2015
    20
    11/8/2015


    This formula entered in D2 and copied down:

    =SUMPRODUCT(--(TEXT(A$2:A$20,"mmmyyyy")=C2&YEAR(NOW())))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 05-26-2014, 03:38 PM #6
    KK1234
    KK1234 is offline
    Forum Contributor
    Join Date
    02-19-2013
    Location
    UK
    MS-Off Ver
    Excel 2019
    Posts
    677

    Re: How to count entries monthwise for current year formula

    Hi Tony Valko,
    Thats wonderful indeed.
    Thanks for your kind help
    Reply With QuoteRegister To Reply
  7. 05-26-2014, 04:03 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to count entries monthwise for current year formula

    You're welcome. Thanks for the feedback!
    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)

Similar Threads

  1. [SOLVED] Count current year monthwise entries formula
    By KK1234 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-26-2014, 10:05 AM
  2. [SOLVED] Count or Sum Number of Entries per Year
    By mycon73 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2014, 02:57 PM
  3. Formula to compare previous year to current year
    By bjnockle in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-08-2013, 04:23 PM
  4. [SOLVED] Formula to count entries as per month, year and submission wise
    By hanidean in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-26-2013, 11:52 PM
  5. [SOLVED] Keeping a date as current year, even though formula determines previous year
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 08:31 PM

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