software

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.

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-Proquot;Jim Jacksonquot; wrote:

gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; want to count the number of times a name occurs within a given date range.

Assuming that they are real dates

=SUMPRODUCT(--(A1:A100=quot;Jimquot;),--(TEXT(B1:B100,quot;mmmyyquot;)=quot;Jan06quot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Jim Jacksonquot; lt;Jim gt; wrote in message
...
gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; want to count the number of times a name occurs within a given date range.

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 yearI am not familiar with the product definition and any help would be
greatly appreciated.

Many thanks in advanceTeacher_unsure--
teacher_unsure
------------------------------------------------------------------------
teacher_unsure's Profile: www.excelforum.com/member.php...oamp;userid=32081
View this thread: www.excelforum.com/showthread...hreadid=518304Assuming the leave date is in column A, and the university flag is in column
B

=SUMPRODUCT(--(YEAR(A1:A100)=1999),--(B1:B100,quot;Yquot;))

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)

quot;teacher_unsurequot;
lt;teacher_unsure.2420up_1141323008.3611@excelforu m-nospam.comgt; wrote in
message news:teacher_unsure.2420up_1141323008.3611@excelfo rum-nospam.com...
gt;
gt; hi there, i have a similar problem to the gentleman above, one of my
gt; studenst wants to firstly look up all the students who left in a
gt; particular year (1999) YYYY, he then wants to find out how many of
gt; them went to university (Y or a N).
gt;
gt; This doesn't have to be all part of the same formula, as he has already
gt; done a countIf statement to find the number of pupils who left school in
gt; the particular year
gt;
gt;
gt; I am not familiar with the product definition and any help would be
gt; greatly appreciated.
gt;
gt; Many thanks in advance
gt;
gt;
gt; Teacher_unsure
gt;
gt;
gt; --
gt; teacher_unsure
gt; ------------------------------------------------------------------------
gt; teacher_unsure's Profile:
www.excelforum.com/member.php...oamp;userid=32081
gt; View this thread: www.excelforum.com/showthread...hreadid=518304
gt;
I should have specified that they are real dates. What I need is quot;Name(A)
occurs quot;xquot; times between 1/1/2006 and 1/31/2006quot;. 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

quot;Ron Coderrequot; wrote:

gt; Do the date ranges actually contain text (JAN06, MAR06, etc)?
gt; or do they contain actual dates (1/12/2006, 2/13/06,etc)?
gt;
gt; It makes a big difference in the kind of solution you need.
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jim Jacksonquot; wrote:
gt;
gt; gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; gt; want to count the number of times a name occurs within a given date range.

=SUMPRODUCT(--(A1:A100=quot;Jimquot;),--(B1:B100gt;=--quot;2006-01-01quot;),--(B1:B100lt;=--quot;200
6-01-31quot;))

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)

quot;Jim Jacksonquot; gt; wrote in message
news
gt; I should have specified that they are real dates. What I need is quot;Name(A)
gt; occurs quot;xquot; times between 1/1/2006 and 1/31/2006quot;. The dates are listed as
gt; 1/1/2006, 1/7/2006 1/8/2006 etc. I can get the formula to work as far as
gt; number of occurences for a single date, but to get them for the date range
is
gt; eluding me.
gt;
gt; The dates in the column might be two in a month or 20 and a particular
name
gt; might occur one time or 6 times.
gt;
gt; Thanks,
gt;
gt; Jim
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Do the date ranges actually contain text (JAN06, MAR06, etc)?
gt; gt; or do they contain actual dates (1/12/2006, 2/13/06,etc)?
gt; gt;
gt; gt; It makes a big difference in the kind of solution you need.
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Jim Jacksonquot; wrote:
gt; gt;
gt; gt; gt; I have one column with names and another with date ranges (JAN06) etc.
I
gt; gt; gt; want to count the number of times a name occurs within a given date
range.
=COUNTIF((B2:B1000),quot;*Fred*quot;)-(COUNTIF((A2:A1000),quot;gt;=3/1/2005quot;)-(COUNTIF((A2:A1000),quot;gt;=4/1/2005quot;)))

This is the formula I am trying to get to work. This does not work as it
stands. It returns a quot;7quot; when there are five dates in the Dates column and
No quot;Fredquot;s in the names column.

I think I am close but can't seem to get any further.

Jim

quot;Ron Coderrequot; wrote:

gt; Do the date ranges actually contain text (JAN06, MAR06, etc)?
gt; or do they contain actual dates (1/12/2006, 2/13/06,etc)?
gt;
gt; It makes a big difference in the kind of solution you need.
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jim Jacksonquot; wrote:
gt;
gt; gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; gt; want to count the number of times a name occurs within a given date range.

If you are looking to match if quot;fredquot; 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(quot;fredquot;,B1:B100)),--(A1:A100gt;=--quot;2005-03-01quot;),--(A1:A100lt;=--quot;2005-03-31quot;))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Jim Jacksonquot; wrote:

gt; =COUNTIF((B2:B1000),quot;*Fred*quot;)-(COUNTIF((A2:A1000),quot;gt;=3/1/2005quot;)-(COUNTIF((A2:A1000),quot;gt;=4/1/2005quot;)))
gt;
gt; This is the formula I am trying to get to work. This does not work as it
gt; stands. It returns a quot;7quot; when there are five dates in the Dates column and
gt; No quot;Fredquot;s in the names column.
gt;
gt; I think I am close but can't seem to get any further.
gt;
gt; Jim
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Do the date ranges actually contain text (JAN06, MAR06, etc)?
gt; gt; or do they contain actual dates (1/12/2006, 2/13/06,etc)?
gt; gt;
gt; gt; It makes a big difference in the kind of solution you need.
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Jim Jacksonquot; wrote:
gt; gt;
gt; gt; gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; gt; gt; want to count the number of times a name occurs within a given date range.

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 quot;3quot; for John's occurences
and a quot;2quot; for Jane's and quot;0quot; for any other name in the complete sheet.

Thanks,

Jim

quot;Ron Coderrequot; wrote:

gt; If you are looking to match if quot;fredquot; is located anywhere in the cell (eg The
gt; Fred Company) for the month of MAR 2005, try using this variant of Bob's
gt; formula:
gt;
gt; =SUMPRODUCT(--ISNUMBER(FIND(quot;fredquot;,B1:B100)),--(A1:A100gt;=--quot;2005-03-01quot;),--(A1:A100lt;=--quot;2005-03-31quot;))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jim Jacksonquot; wrote:
gt;
gt; gt; =COUNTIF((B2:B1000),quot;*Fred*quot;)-(COUNTIF((A2:A1000),quot;gt;=3/1/2005quot;)-(COUNTIF((A2:A1000),quot;gt;=4/1/2005quot;)))
gt; gt;
gt; gt; This is the formula I am trying to get to work. This does not work as it
gt; gt; stands. It returns a quot;7quot; when there are five dates in the Dates column and
gt; gt; No quot;Fredquot;s in the names column.
gt; gt;
gt; gt; I think I am close but can't seem to get any further.
gt; gt;
gt; gt; Jim
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Do the date ranges actually contain text (JAN06, MAR06, etc)?
gt; gt; gt; or do they contain actual dates (1/12/2006, 2/13/06,etc)?
gt; gt; gt;
gt; gt; gt; It makes a big difference in the kind of solution you need.
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Jim Jacksonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have one column with names and another with date ranges (JAN06) etc. I
gt; gt; gt; gt; want to count the number of times a name occurs within a given date range.