# Count rows if month is within two different dates

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.

2. ## Re: Count rows if month is within two different dates

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

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

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

3. ## 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

4. ## 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.``

5. ## 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. ## 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.``

7. ## 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:
`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. ## Re: Count rows if month is within two different dates

csondagar - by the way - welcome to the forum!

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

#### 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