+ Reply to Thread
Results 1 to 3 of 3

formula needed - 2 dates fields and summarizing to count/month

  1. #1
    Registered User
    Join Date
    10-21-2019
    Location
    Boston, MA
    MS-Off Ver
    Excel for Mac v 16.29.1
    Posts
    1

    Question formula needed - 2 dates fields and summarizing to count/month

    Hi All,

    I'm struggling with a formula.

    I have 2 date fields:

    Cell D1 - 10/1/19
    Cell E1 - 11/1/19

    On another tab in the workbook (Summary) I have Months listed in column A;
    January
    February
    March
    etc. through December

    I'd like to be able to allocate a "1" to the Summary tab, Column B counting each time a date falls within that month.

    For example:
    Cell D1 - 10/1/19
    Cell D2 - 11/1/19

    Cell E1 - 11/1/19
    Cell E2 - 12/15/19

    Summary Tab would display
    October - 1
    November - 2
    December - 1

    Any ideas on the formula I can utilize? I'm thinking it's an IF(AND( but can't get there.

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,220

    Re: formula needed - 2 dates fields and summarizing to count/month

    its relatively straight forward, as long as your months are dates and not text, but really an attachment with some sample data and expected results will get you a better answer

    the answer will be arounf countifs or sumproduct.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: formula needed - 2 dates fields and summarizing to count/month

    =SUMPRODUCT((TEXT(Dates!D$1:D$1000,"mmmm")=Summary!D1)+(TEXT(Dates!B$1:B$100,"mmmm")=Summary!D1))
    copy down the column

    where your date ranges are ona sheet called Dates
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help Needed: Formula Dealing with Dates/Current Month
    By IAMROBBERMAN in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-08-2019, 11:22 AM
  2. [SOLVED] Week count from dates formula Help needed
    By MattExcelLearner in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-05-2019, 10:05 AM
  3. [SOLVED] Sum Total Costs for Each Month Based between the Phase Dates Formula Needed
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-18-2017, 09:16 PM
  4. Replies: 3
    Last Post: 11-15-2015, 04:23 PM
  5. [SOLVED] Countifs Formula needed to count # of dates in same month that also is in same state.
    By Stanimal in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-12-2014, 02:43 PM
  6. [SOLVED] Formula needed to calculate working days in specified month between two dates
    By webfeet2 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2013, 02:19 AM
  7. Replies: 5
    Last Post: 05-12-2009, 09:37 AM

Tags for this Thread

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