If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
count function problem
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lookup function problem (kg) | greencecil | Worksheet Functions | 3 | July 1st, 2005 04:54 PM |
Excel option to store trendline's coefficients in cells for use | Miguel Saldana | Charts and Charting | 9 | June 20th, 2005 08:45 PM |
How do I add the count function to the toolbar? | smeesh | Worksheet Functions | 8 | March 31st, 2005 07:51 AM |
IF function problem | dvonj | Worksheet Functions | 13 | March 10th, 2005 02:13 PM |
Count function problem | Christo | Worksheet Functions | 6 | February 12th, 2004 11:15 PM |