A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

count function problem



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2005, 10:02 PM
nkidd
external usenet poster
 
Posts: n/a
Default 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  
Old July 6th, 2005, 10:09 PM
bj
external usenet poster
 
Posts: n/a
Default

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  
Old July 7th, 2005, 04:52 PM
nkidd
external usenet poster
 
Posts: n/a
Default

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  
Old July 7th, 2005, 08:50 PM
bj
external usenet poster
 
Posts: n/a
Default

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  
Old July 7th, 2005, 08:55 PM
bj
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.