counting multiple occurances

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

 

[Jason Morin]

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

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?

 

[Guest]

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.