Counting occurrences in one column based on an occurrence in anoth

J

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

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
 
B

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)
 
T

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
 
B

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"
 
J

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
 
B

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 said:
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
Click to expand...
range.
 
J

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
 
R

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
 
J

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
 
J

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
 
J

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
 
R

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
 
Top