I am trying to make a spreadsheet that counts how many times a certain name
in collum A and a certain number in collum B appear. In collum A, I have a
list of people's names. Each name may appear multiple times. In collum B, I
have a list of numbers ranging from 1-12 which stand for months (1 for Jan, 2
for Feb, etc). On another worksheet, I have each person's name in collum A
listed once. In collum's B-M i have the months listed out at the top as
collum labels. Collum N is used for a "total". I need to have it count how
many times a certain person's name appears next to each number and put this
in the correct month. For example lets say the name "Kevin" appears 4 times
on the list on the first worksheet. The first two times the name has a 3
next to it in the number collum, one time has a 10 and the other time it has
a 4 next to it. On the 2nd worksheet in the Kevin row, I need it to put in a
2 in the March collum, 1 in the October collum, and a 1 in the April collum.
Please let me know if you need any more information to help me. Thank you
very much.

In sheet2
in B1 enter
=sumproduct(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=Row()-1))
copy to all of the names and months

"nkidd" wrote:

I am trying to make a spreadsheet that counts how many times a certain name
in collum A and a certain number in collum B appear. In collum A, I have a
list of people's names. Each name may appear multiple times. In collum B, I
have a list of numbers ranging from 1-12 which stand for months (1 for Jan, 2
for Feb, etc). On another worksheet, I have each person's name in collum A
listed once. In collum's B-M i have the months listed out at the top as
collum labels. Collum N is used for a "total". I need to have it count how
many times a certain person's name appears next to each number and put this
in the correct month. For example lets say the name "Kevin" appears 4 times
on the list on the first worksheet. The first two times the name has a 3
next to it in the number collum, one time has a 10 and the other time it has
a 4 next to it. On the 2nd worksheet in the Kevin row, I need it to put in a
2 in the March collum, 1 in the October collum, and a 1 in the April collum.
Please let me know if you need any more information to help me. Thank you
very much.

Ok, i have January working good, but i cant figure out what I need to change
to get the rest of the months to work. Here is the exact formula i am using
for the first name =SUMPRODUCT(--('Leave As Is'!$A$1:$A$1000=$A2),--('Leave
As Is'!$D$1:$D$1000=ROW()-1)) and the second name is =SUMPRODUCT(--('Leave
As Is'!$A$1:$A$1000=$A3),--('Leave As Is'!$D$1:$D$1000=ROW()-2)) etc. This
works perfectly to give me how many 1's there are for January, but what do i
change to get the 2's for Feb, 3's for March, etc?

"bj" wrote:

In sheet2
in B1 enter
=sumproduct(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=Row()-1))
copy to all of the names and months

"nkidd" wrote:

I am trying to make a spreadsheet that counts how many times a certain name
in collum A and a certain number in collum B appear. In collum A, I have a
list of people's names. Each name may appear multiple times. In collum B, I
have a list of numbers ranging from 1-12 which stand for months (1 for Jan, 2
for Feb, etc). On another worksheet, I have each person's name in collum A
listed once. In collum's B-M i have the months listed out at the top as
collum labels. Collum N is used for a "total". I need to have it count how
many times a certain person's name appears next to each number and put this
in the correct month. For example lets say the name "Kevin" appears 4 times
on the list on the first worksheet. The first two times the name has a 3
next to it in the number collum, one time has a 10 and the other time it has
a 4 next to it. On the 2nd worksheet in the Kevin row, I need it to put in a
2 in the March collum, 1 in the October collum, and a 1 in the April collum.
Please let me know if you need any more information to help me. Thank you
very much.

leave it as row()-1 for all of the columns
Assuming the months are Jan in B Feb in C etc. in the new sheet
Col B is Column 2

"nkidd" wrote:

Ok, i have January working good, but i cant figure out what I need to change
to get the rest of the months to work. Here is the exact formula i am using
for the first name =SUMPRODUCT(--('Leave As Is'!$A$1:$A$1000=$A2),--('Leave
As Is'!$D$1:$D$1000=ROW()-1)) and the second name is =SUMPRODUCT(--('Leave
As Is'!$A$1:$A$1000=$A3),--('Leave As Is'!$D$1:$D$1000=ROW()-2)) etc. This
works perfectly to give me how many 1's there are for January, but what do i
change to get the 2's for Feb, 3's for March, etc?

"bj" wrote:

In sheet2
in B1 enter
=sumproduct(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=Row()-1))
copy to all of the names and months

"nkidd" wrote:

I am trying to make a spreadsheet that counts how many times a certain name
in collum A and a certain number in collum B appear. In collum A, I have a
list of people's names. Each name may appear multiple times. In collum B, I
have a list of numbers ranging from 1-12 which stand for months (1 for Jan, 2
for Feb, etc). On another worksheet, I have each person's name in collum A
listed once. In collum's B-M i have the months listed out at the top as
collum labels. Collum N is used for a "total". I need to have it count how
many times a certain person's name appears next to each number and put this
in the correct month. For example lets say the name "Kevin" appears 4 times
on the list on the first worksheet. The first two times the name has a 3
next to it in the number collum, one time has a 10 and the other time it has
a 4 next to it. On the 2nd worksheet in the Kevin row, I need it to put in a
2 in the March collum, 1 in the October collum, and a 1 in the April collum.
Please let me know if you need any more information to help me. Thank you
very much.

or you could have changed the
=SUMPRODUCT(--('Leave As Is'!$A$1:$A$1000=$A2),--('Leave As
Is'!$D$1:$D$1000=1)) for Jan
....=2)) for feb
....=3)) for Mar etc
"nkidd" wrote:
for the second name the equation should not have ended as -2)) but rather as
-1))

Ok, i have January working good, but i cant figure out what I need to change
to get the rest of the months to work. Here is the exact formula i am using
for the first name =SUMPRODUCT(--('Leave As Is'!$A$1:$A$1000=$A2),--('Leave
As Is'!$D$1:$D$1000=ROW()-1)) and the second name is =SUMPRODUCT(--('Leave
As Is'!$A$1:$A$1000=$A3),--('Leave As Is'!$D$1:$D$1000=ROW()-2)) etc. This
works perfectly to give me how many 1's there are for January, but what do i
change to get the 2's for Feb, 3's for March, etc?

"bj" wrote:

In sheet2
in B1 enter
=sumproduct(--(Sheet1!$A$1:$A$1000=$A2),--(Sheet1!$B$1:$B$1000=Row()-1))
copy to all of the names and months

"nkidd" wrote:

I am trying to make a spreadsheet that counts how many times a certain name
in collum A and a certain number in collum B appear. In collum A, I have a
list of people's names. Each name may appear multiple times. In collum B, I
have a list of numbers ranging from 1-12 which stand for months (1 for Jan, 2
for Feb, etc). On another worksheet, I have each person's name in collum A
listed once. In collum's B-M i have the months listed out at the top as
collum labels. Collum N is used for a "total". I need to have it count how
many times a certain person's name appears next to each number and put this
in the correct month. For example lets say the name "Kevin" appears 4 times
on the list on the first worksheet. The first two times the name has a 3
next to it in the number collum, one time has a 10 and the other time it has
a 4 next to it. On the 2nd worksheet in the Kevin row, I need it to put in a
2 in the March collum, 1 in the October collum, and a 1 in the April collum.
Please let me know if you need any more information to help me. Thank you
very much.