Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
teacher_unsure
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jim Jackson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with copying dynamic column selected based on remote cell value and dynamic formula fill ers Charts and Charting in Excel 0 March 1st 06 02:05 AM
count values in one column based on their realtionship with anoth. shopaholic Excel Worksheet Functions 1 December 9th 05 02:13 AM
Counting based on other cells contents... George Excel Discussion (Misc queries) 3 November 8th 05 03:33 PM
Complicated counting of cells (based on other cells contents) George Excel Worksheet Functions 3 November 7th 05 07:39 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM


All times are GMT +1. The time now is 08:53 AM.

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

About Us

"It's about Microsoft Excel"