|
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! |
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! |
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! |
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! |
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:D$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 |
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! |
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! |
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! |
| All times are GMT +1. The time now is 11:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com