+ 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
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    747

    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
    Valued Forum Contributor Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    BKK, Thailand
    MS-Off Ver
    Excel365
    Posts
    2,224

    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
    MS-Off Ver
    Excel 2007
    Posts
    4,454

    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
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    747

    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
    MS-Off Ver
    Excel 2007
    Posts
    4,454

    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
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    747

    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
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    747

    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)

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