counting multiple occurances

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have different people completing a number of tasks per day. I would like to be able to find out how many tasks each person completed each day.

Sheet A has a column containing the names. Another column has the number of the day.

Sheet B has a column of the names and 31 columns representing each day of the month.

How do I count the number of times a name shows up per day and write that number to another cel?

Example:

Sheet1: Col A has list of names. Col B has the day of the month

A B
1 Name 1 1
2 Name 1 1
3 Name 2 1
4 Name 1 1
5 Name 2 2
6 Name 3 2
7 Name 1 3
8 Name 3 3
9 Name 2 3

Sheet2: Col A has list of names. Cols C thru AG are the days of the month

A B C D E F G
1 1 2 3 4 5
2 Name 1 3 1
3 Name 2 1 1 1
4 Name 3 1 1
5 Name 4

I beleive it requires nested IF statements combined with COUNTIF?

I tried the following in cel Sheet2!C2 which didn't work:
=IF(Sheet1!A:A=Sheet2!A2, COUNTIF(Sheet1!B:B, Sheet2!C1))
also
=SUM(IF(Sheet1!A:A=Sheet2!A2, IF(Sheet1!B:B=Sheet2!C1)))

I need to be able to count each name and how many times that name shows up per day.

Thanks for anything and everything.
 
Try this in B2 of Sheet2, and fill across and down:

=SUMPRODUCT((Sheet1!$A$1:$A$9=$A2)*(Sheet1!
$B$1:$B$9=Sheet2!B$1))

HTH
Jason
Atlanta, GA
-----Original Message-----
I have different people completing a number of tasks per
Click to expand...
day. I would like to be able to find out how many tasks
each person completed each day.
Sheet A has a column containing the names. Another column has the number of the day.

Sheet B has a column of the names and 31 columns
Click to expand...
representing each day of the month.
How do I count the number of times a name shows up per
Click to expand...
day and write that number to another cel?
 
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 formula that ultimately worked for me (entered, of course, by pressing Ctrl + Shift + Enter):

=SUM(IF(Sheet1!$A$1:$A$1000=Sheet2!$A2, IF(Sheet1!$B1:$B1000=Sheet2!C$1,1,0)))

----- Phillip wrote: -----

I have different people completing a number of tasks per day. I would like to be able to find out how many tasks each person completed each day.

Sheet A has a column containing the names. Another column has the number of the day.

Sheet B has a column of the names and 31 columns representing each day of the month.

How do I count the number of times a name shows up per day and write that number to another cel?

Example:

Sheet1: Col A has list of names. Col B has the day of the month

A B
1 Name 1 1
2 Name 1 1
3 Name 2 1
4 Name 1 1
5 Name 2 2
6 Name 3 2
7 Name 1 3
8 Name 3 3
9 Name 2 3

Sheet2: Col A has list of names. Cols C thru AG are the days of the month

A B C D E F G
1 1 2 3 4 5
2 Name 1 3 1
3 Name 2 1 1 1
4 Name 3 1 1
5 Name 4

I beleive it requires nested IF statements combined with COUNTIF?

I tried the following in cel Sheet2!C2 which didn't work:
=IF(Sheet1!A:A=Sheet2!A2, COUNTIF(Sheet1!B:B, Sheet2!C1))
also
=SUM(IF(Sheet1!A:A=Sheet2!A2, IF(Sheet1!B:B=Sheet2!C1)))

I need to be able to count each name and how many times that name shows up per day.

Thanks for anything and everything.
 
Back
Top