If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 How to count dates within a certain range in a column with mutiple date range entries
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How to count dates within a certain range in a column with mutiple date range entries

#1
August 31st 05, 08:31 PM
 Krisjhn external usenet poster Posts: n/a
How to count dates within a certain range in a column with mutiple date range entries

On my speedsheet I have two worksheets A and B

WORKSHEET B

Column J2:J528 entitled -1st RCVD Date-

In this column I will have a variety of dates spanning over three
diffferent months

WORKSHEET A

Column N21 titled -# of Persons-
Column L22:L29 is Titled Weeks - Each row is broken down by week
L22/8/17/05, L23 8/24/05. L24 8/31/05, etc to L29 10/5/05

What I am looking for is a count of number of persons by week r1st

So for example if in Worksheet B in cell J2 I have a date that lies
between 8/17/05 and 8/23/05 a 1 would show up on Workseet A in #of
persons in the row for that corresponding week. Say the next week I
have 6 entires of dates that land in that next week then the number 6
would show up on worksheet A on the row for 8/24/05. And so on.

So the question is how do I add up dates within a certain range in the
same column and then break that down by week to be reported on a
different worksheet by week.

Krisjhn

--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile: http://www.excelforum.com/member.php...o&userid=26796

#2
August 31st 05, 11:15 PM
 Biff external usenet poster Posts: n/a

Hi!

Try this formula in N22:

=SUMPRODUCT(--(Sheet2!J\$2:J\$528>=L22),--(Sheet2!J\$2:J\$528<=L22+6))

Copy down to N29.

This assumes that you are using true dates and not just text strings that
look like dates.

Biff

"Krisjhn" > wrote in
message ...
>
> On my speedsheet I have two worksheets A and B
>
> WORKSHEET B
>
> Column J2:J528 entitled -1st RCVD Date-
>
> In this column I will have a variety of dates spanning over three
> diffferent months
>
> WORKSHEET A
>
> Column N21 titled -# of Persons-
> Column L22:L29 is Titled Weeks - Each row is broken down by week
> L22/8/17/05, L23 8/24/05. L24 8/31/05, etc to L29 10/5/05
>
> What I am looking for is a count of number of persons by week r1st
>
> So for example if in Worksheet B in cell J2 I have a date that lies
> between 8/17/05 and 8/23/05 a 1 would show up on Workseet A in #of
> persons in the row for that corresponding week. Say the next week I
> have 6 entires of dates that land in that next week then the number 6
> would show up on worksheet A on the row for 8/24/05. And so on.
>
> So the question is how do I add up dates within a certain range in the
> same column and then break that down by week to be reported on a
> different worksheet by week.
>
>
> Krisjhn
>
>
> --
> Krisjhn
> ------------------------------------------------------------------------
> Krisjhn's Profile:
> http://www.excelforum.com/member.php...o&userid=26796
>

#3
September 1st 05, 01:59 PM
 Krisjhn external usenet poster Posts: n/a

Thanks Biff. Worked like a charm.

--
Krisjhn
------------------------------------------------------------------------
Krisjhn's Profile: http://www.excelforum.com/member.php...o&userid=26796

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM Compare range of dates and sum... SAL Excel Discussion (Misc queries) 5 March 22nd 05 02:59 PM Find (Today-21) in a range of dates JG Excel Discussion (Misc queries) 4 March 15th 05 03:59 PM Sum between a range of dates Bruce Excel Worksheet Functions 3 January 5th 05 02:25 AM Excel: How to return count for each cell within date range criter. Louisa Excel Worksheet Functions 0 November 5th 04 11:58 AM

All times are GMT +1. The time now is 12:15 AM.