Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in anoth
I have one column with names and another with date ranges (JAN06) etc. I
want to count the number of times a name occurs within a given date range. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in anoth
Do the date ranges actually contain text (JAN06, MAR06, etc)?
or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in anoth
Assuming that they are real dates
=SUMPRODUCT(--(A1:A100="Jim"),--(TEXT(B1:B100,"mmmyy")="Jan06")) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jim Jackson" <Jim wrote in message ... I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in anoth
hi there, i have a similar problem to the gentleman above, one of my studenst wants to firstly look up all the students who left in a particular year (1999) YYYY, he then wants to find out how many of them went to university (Y or a N). This doesn't have to be all part of the same formula, as he has already done a countIf statement to find the number of pupils who left school in the particular year I am not familiar with the product definition and any help would be greatly appreciated. Many thanks in advance Teacher_unsure -- teacher_unsure ------------------------------------------------------------------------ teacher_unsure's Profile: http://www.excelforum.com/member.php...o&userid=32081 View this thread: http://www.excelforum.com/showthread...hreadid=518304 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in anoth
Assuming the leave date is in column A, and the university flag is in column
B =SUMPRODUCT(--(YEAR(A1:A100)=1999),--(B1:B100,"Y")) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "teacher_unsure" <teacher_unsure.2420up_1141323008.3611@excelforu m-nospam.com wrote in message news:teacher_unsure.2420up_1141323008.3611@excelfo rum-nospam.com... hi there, i have a similar problem to the gentleman above, one of my studenst wants to firstly look up all the students who left in a particular year (1999) YYYY, he then wants to find out how many of them went to university (Y or a N). This doesn't have to be all part of the same formula, as he has already done a countIf statement to find the number of pupils who left school in the particular year I am not familiar with the product definition and any help would be greatly appreciated. Many thanks in advance Teacher_unsure -- teacher_unsure ------------------------------------------------------------------------ teacher_unsure's Profile: http://www.excelforum.com/member.php...o&userid=32081 View this thread: http://www.excelforum.com/showthread...hreadid=518304 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
I should have specified that they are real dates. What I need is "Name(A)
occurs "x" times between 1/1/2006 and 1/31/2006". The dates are listed as 1/1/2006, 1/7/2006 1/8/2006 etc. I can get the formula to work as far as number of occurences for a single date, but to get them for the date range is eluding me. The dates in the column might be two in a month or 20 and a particular name might occur one time or 6 times. Thanks, Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
=SUMPRODUCT(--(A1:A100="Jim"),--(B1:B100=--"2006-01-01"),--(B1:B100<=--"200
6-01-31")) if you are only interested in whole months you can use the formula I gave in my previous post. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jim Jackson" wrote in message ... I should have specified that they are real dates. What I need is "Name(A) occurs "x" times between 1/1/2006 and 1/31/2006". The dates are listed as 1/1/2006, 1/7/2006 1/8/2006 etc. I can get the formula to work as far as number of occurences for a single date, but to get them for the date range is eluding me. The dates in the column might be two in a month or 20 and a particular name might occur one time or 6 times. Thanks, Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
=COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005")))
This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
If you are looking to match if "fred" is located anywhere in the cell (eg The
Fred Company) for the month of MAR 2005, try using this variant of Bob's formula: =SUMPRODUCT(--ISNUMBER(FIND("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: =COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005"))) This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
No, the names column has first names only. In the date range I have set in
the formula, there are five dates with a couple of different names, one per row, for the five rows. I am trying to get a formula to tell me how many times that name appears in the specified date range. A B John 3/5/2005 Jane 3/15/2005 John 3/20,2005 John 3/27/2005 Jane 3/31/2005 The above is a representation of the spreadsheet, at least the columns with the pertinent data. I need the formula to return a "3" for John's occurences and a "2" for Jane's and "0" for any other name in the complete sheet. Thanks, Jim "Ron Coderre" wrote: If you are looking to match if "fred" is located anywhere in the cell (eg The Fred Company) for the month of MAR 2005, try using this variant of Bob's formula: =SUMPRODUCT(--ISNUMBER(FIND("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: =COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005"))) This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
I answered too soon it seems. After shooting back my reply I thought to try
the formula. It has worked each of the several times I have tried it with varying date ranges and names. Thanks, Jim "Ron Coderre" wrote: If you are looking to match if "fred" is located anywhere in the cell (eg The Fred Company) for the month of MAR 2005, try using this variant of Bob's formula: =SUMPRODUCT(--ISNUMBER(FIND("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: =COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005"))) This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
The formula works so well it solves another problem. There are instances
where two names appear such as John/Jane instead of the usual single name. Both names need to be credited with that appearance and this formula takes care of that as well. Thanks you for helping save my sanity. Jim "Ron Coderre" wrote: If you are looking to match if "fred" is located anywhere in the cell (eg The Fred Company) for the month of MAR 2005, try using this variant of Bob's formula: =SUMPRODUCT(--ISNUMBER(FIND("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: =COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005"))) This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting occurrences in one column based on an occurrence in a
I'm glad you got that to work for you.
One side comment: If you don't want the formula to be case sensitive replace FIND with SEARCH as in: =SUMPRODUCT(--ISNUMBER(SEARCH("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: The formula works so well it solves another problem. There are instances where two names appear such as John/Jane instead of the usual single name. Both names need to be credited with that appearance and this formula takes care of that as well. Thanks you for helping save my sanity. Jim "Ron Coderre" wrote: If you are looking to match if "fred" is located anywhere in the cell (eg The Fred Company) for the month of MAR 2005, try using this variant of Bob's formula: =SUMPRODUCT(--ISNUMBER(FIND("fred",B1:B100)),--(A1:A100=--"2005-03-01"),--(A1:A100<=--"2005-03-31")) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: =COUNTIF((B2:B1000),"*Fred*")-(COUNTIF((A2:A1000),"=3/1/2005")-(COUNTIF((A2:A1000),"=4/1/2005"))) This is the formula I am trying to get to work. This does not work as it stands. It returns a "7" when there are five dates in the Dates column and No "Fred"s in the names column. I think I am close but can't seem to get any further. Jim "Ron Coderre" wrote: Do the date ranges actually contain text (JAN06, MAR06, etc)? or do they contain actual dates (1/12/2006, 2/13/06,etc)? It makes a big difference in the kind of solution you need. *********** Regards, Ron XL2002, WinXP-Pro "Jim Jackson" wrote: I have one column with names and another with date ranges (JAN06) etc. I want to count the number of times a name occurs within a given date range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
count values in one column based on their realtionship with anoth. | Excel Worksheet Functions | |||
Counting based on other cells contents... | Excel Discussion (Misc queries) | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions |