+ Reply to Thread
Results 1 to 8 of 8

Count rows if month is within two different dates

  1. #1
    Registered User
    Join Date
    01-12-2019
    Location
    Somewhere
    MS-Off Ver
    2016
    Posts
    1

    Count rows if month is within two different dates

    Hi All

    I am trying to get count rows that are within certain month as specified in the Metrics tab.

    The uploaded sample includes 2 tabs - Resources and Metrics. Resources is a list of Person records and their Start date, and if application End date. A person can existing multiple times if they have non-overlapping start and end dates. The second tab is Metrics where I want to count number of rows if the Month (Row 1) is within Start and End dates.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Count rows if month is within two different dates

    This needs a helper column: Resources!G
    In Resources cell G2 copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Then in metrics!B2 and copied across:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And somewhat similar formulas in B3, B5 and B6. See the attached workbook.
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count rows if month is within two different dates

    Please try at B2 copy till R3

    =SUMPRODUCT(COUNTIFS(Resources!$E$2:$E$40,"<="&Metrics!B$1,Resources!$F$2:$F$40,{">=",""}&IFERROR(EOMONTH(B$1,0)/{1,0},""),INDEX(Resources!$C$2:$D$40,,MATCH(LEFT($A2,FIND(" ",$A2)-1),Resources!$C$1:$D$1,)),1,Resources!$B$2:$B$40,SUBSTITUTE(MID($A2,FIND(" ",$A2)+1,20),"s","")))

    and copy B2:R3 to B5
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count rows if month is within two different dates

    Try this. In B2 then copied to other required cells.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Count rows if month is within two different dates

    kvsrinivasamurthy: As far as I can see, I think your checks against start and end dates are incorrect. The following corrections make it work I believe:

    =SUMPRODUCT((ISNUMBER(FIND(Resources!$B$2:$B$40,$A2)))*((Resources!$C$2:$C$40=ISNUMBER(FIND("Capital",$A2)))+(Resources!$D$2:$D$40=ISNUMBER(FIND("Operating",$A2))))*(Resources!$E$2:$E$40<B$1)*((Resources!$F$2:$F$40>=EOMONTH(B$1,0)) + (Resources!$F$2:$F$40 = "" )) )

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count rows if month is within two different dates

    Yes mr GeoffW283 .
    Sorry for the mistake. Corrected formula is here.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Count rows if month is within two different dates

    kvsrinivasamurthy: I think there is still a problem. You need to accommodate both a finish date in column-F as well as an empty cell in column-F. Hence the
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in red at the end of my post #5.

    I'd usually think it's my mistake but my solution in post #2 and Bo's solution in post #3 both agree with each other via very different approaches.

  8. #8
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Count rows if month is within two different dates

    csondagar - by the way - welcome to the forum!

+ 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. [SOLVED] Month Count using Two Dates
    By nevernine9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-25-2018, 11:08 AM
  2. Count dates that are in month
    By nathanexcelhelp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-29-2016, 08:16 AM
  3. [SOLVED] Need help: Dates count in month
    By cdllt in forum Excel General
    Replies: 17
    Last Post: 11-19-2015, 08:52 PM
  4. [SOLVED] Count how many dates are within each month
    By Smally in forum Excel General
    Replies: 6
    Last Post: 06-18-2015, 07:21 AM
  5. [SOLVED] Count Dates In A Month
    By ajocius in forum Excel General
    Replies: 6
    Last Post: 02-02-2015, 05:14 PM
  6. count dates per month without recurrence
    By KALATRASH81 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-28-2014, 08:44 AM
  7. Count the dates in a month
    By s2m via OfficeKB.com in forum Excel General
    Replies: 5
    Last Post: 08-04-2006, 12:40 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1