Counting occurrences in one column based on an occurrence in anoth

[Jim Jackson]

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.

[Ron Coderre]

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.

[Bob Phillips]

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 [email protected]> wrote in message
news:[email protected]...
> 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.

[teacher_unsure]

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

[Bob Phillips]

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jim Jackson]

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.

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Jim Jackson]

=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.

[Ron Coderre]

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.

[Jim Jackson]

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.

[Jim Jackson]

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.

[Jim Jackson]

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.

[Ron Coderre]

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.