counting number of occurances

  • Thread starter Thread starter Phillip
  • Start date Start date
P

Phillip

I'm fairly new to Excel and unsure where to even begin... I
would like to know how to count the number of occurances
there are based on 2 sets of criteria.

Worksheet 1:
Column A has different dates for each day of the month
Column B has different names i.e. Phillip, Corey, Emily,
etc.

Worksheet 2:
Column A has a list of names.
Column B needs to display how many times the name is listed
per date from Worksheet 1 column A.

For example: Column A1:A10 has the date 12/23/2003 10
times. Column B1:B10 has the name Phillip 3 times, Corey 5
times, Emily 2 times. How do I count how many of the same
names there are on a specific date and display that number
on Worksheet 2 column B?

Thanks for any help or guidance.
Phillip
 
Hi Phillip,
This sounds like a good candidate for Pivot Table. Read Help and come back
with specific questions.
Bernard
 
Not sure I explained it well enough.

Worksheet 1:
Column A has different dates for each day of the month
Column B has different names i.e. Phillip, Corey, Emily

Worksheet 2:
Column A has list of all the different names possible
Columns B thru AF represent each day of the month (1-31)

If Worksheet 1 Column A1:A10 has a specific date(s) and
Column B1:B10 has different names, how do I count how many
of the same names there are on a specific date and display
that number on Worksheet 2 column B?

Therefore, the date 12/02/2003 would correspond to column
C, 12/03/2003 is column d, etc.

Worksheet 1:
A B
12/01/2003 Phillip
12/01/2003 Phillip
12/01/2003 Corey
12/01/2003 Emily
12/01/2003 Corey
12/01/2003 Corey
12/01/2003 Emily
12/01/2003 Phillip
12/01/2003 Corey
12/01/2003 Corey
12/03/2003 Phillip
12/03/2003 Corey

Worksheet 2: (should display like this)
A B C D
Day 1 Day 2 Day 3
Phillip 3 0 1
Corey 5 0 1
Emily 2 0 0

Again... thanks for any and all help. :-)
Phillip
 
Ho Phillip,

For your excellent description of the problem, here's one way...

In cell B2 on Sheet2, enter

=SUMPRODUCT(--(B$1=Sheet1!$A1:$A1000),--($A2=Sheet1!$B1:$B1000))

then fill down and across as needed. It works on your sample data.

Please note,
- data in Sheet1 begins in A1
- on Sheet2, there must be dates in row 1, starting in column B

HTH
Anders Silven
 
In case anyone ever goes back and searches these threads... I figured out the answer to my problem. I had tried numerous formulas, configurations, etc. Someone at work finally helped me realize that I was trying to solve an "array formula." I had been neglecting to press the Ctrl + Shift keys when pressing Enter key to inform Excel it was an array formula.

Here is the original problem:

Worksheet 1:
Column A has different names i.e. Phillip, Corey, Emily, etc.
Column B has different dates for each day of the month

Worksheet 2:
Column A has a list of names.
Column B needs to display how many times the name is listed per date from Worksheet 1 column A.

Here is the formula that ultimately worked for me (entered, of course, by pressing Ctrl + Shift + Enter):

=SUM(IF(Sheet1!$A$1:$A$1000=Sheet2!$A1, IF(Sheet1!$B1:$B1000=Sheet2!B$1,1,0)))
 
Back
Top