Remember Me?

#### Menu

#1
June 4th 07, 03:51 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 15
find dates between in excel

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

#2
June 4th 07, 04:04 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 4,339
find dates between in excel

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))

You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

#3
June 4th 07, 04:09 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,572
find dates between in excel

With names in Column A, and dates in Column B, one way would be to enter a
name to count in C1, and the number of the month (Jan=1, Feb=2, ... etc.) in
C2, and try something like this:

=SUMPRODUCT((A1:A50=C1)*(MONTH(B1:B50)=C2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"MDI Anne" wrote in message
...
I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some
other
formula, I would love some help.

Thanks all, in advance!

#4
June 4th 07, 04:38 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 15
find dates between in excel

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))

You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

#5
June 4th 07, 04:38 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 2,202
find dates between in excel

With names in Column A, and dates in Column B, one way would be to enter a
name to count in C1, and the number of the month (Jan=1, Feb=2, ... etc.)
in
C2, and try something like this:

Another possibility which is more "visual" (you will be able to see the
matching rows). Assuming the same set up above, put this formula in D1...

=A1&" - "&MONTH(B1)

and copy down to the end of your data. Then put this formula in E1...

=IF(AND(A1=C\$1,MONTH(B1)=C\$2),COUNTIF(D\$1\$10000, "="&A1&" - "&C\$2),"")

(using a number larger than the number of rows of data you have for the
10000 I used) and copy down to the end of your data (or beyond if your data
can grow). Now, when you put a name in C1 and a month number in C2, each
matching row will be flagged in column 'E' with the total number of hits for
that match.

Rick

#6
June 4th 07, 04:49 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 4,339
find dates between in excel

Sorry ... my typo ....

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--(YEAR(B1:B27)=2007))

"MDI Anne" wrote:

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))

You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

#7
June 4th 07, 06:06 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 15
find dates between in excel

Excellent!!! Works like a charm! Thank you!!

"Toppers" wrote:

Sorry ... my typo ....

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--(YEAR(B1:B27)=2007))

"MDI Anne" wrote:

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))

You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

#8
June 4th 07, 06:25 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 103
find dates between in excel

hi anne,
by using filters u could do this.You can find the filter option in
DataFilterAuto Filter. By clicking this you could find dropdowns come in
the first row.There u cold go for custom where u could give two dates and
thus could filter the range of datas to clear the filter click on the
dropdown and give all values.

Regards
Arun

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007

I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post LiLi Excel Discussion (Misc queries) 3 September 15th 06 04:38 AM JJ Excel Worksheet Functions 3 May 16th 06 09:36 AM Candy Excel Discussion (Misc queries) 2 January 31st 06 03:32 PM sjayar Excel Discussion (Misc queries) 3 November 3rd 05 07:24 AM JR Hester Excel Worksheet Functions 3 November 1st 05 10:17 PM

All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017