| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count data in 2 columns
Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
=SUMIF(A:A,"John Doe",B:B)
-- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Ltat42a" wrote in message ... Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
=sumproduct(--(A1:A500)="John Doe"),--(B1:B100))
If "John Doe" is in a cell e.g C1, then replace literal by cell =sumproduct(--(A1:A500)=C1),--(B1:B100)) HTH "Ltat42a" wrote: Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Thanx for the assistance. This worked for me - =SUMIF(A:A,"John Doe",B:B) This didn't - =sumproduct(--(A1:A500)="John Doe"),--(B1:B100)) If "John Doe" is in a cell e.g C1, then replace literal by cell =sumproduct(--(A1:A500)=C1),--(B1:B100)) When I used these, I kept getting the #VALUE! error. My list of names is in A2:A60, the corresponding numbers are in B2:B60. I'm using Excel 2003. Thanx -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Toppers had a typo in the SumProduct formula.
*All* ranges must be of equal size. A1:A500 and B1:B500 should work. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ltat42a" wrote in message ... Thanx for the assistance. This worked for me - =SUMIF(A:A,"John Doe",B:B) This didn't - =sumproduct(--(A1:A500)="John Doe"),--(B1:B100)) If "John Doe" is in a cell e.g C1, then replace literal by cell =sumproduct(--(A1:A500)=C1),--(B1:B100)) When I used these, I kept getting the #VALUE! error. My list of names is in A2:A60, the corresponding numbers are in B2:B60. I'm using Excel 2003. Thanx -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Still no luck, keep getting the #value! error. My data is in the correct columns, the formula is looking in the correct columns, but I keep getting the value error. Thanx again for the reply... -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Since you say that the list is extensive, you could simply return the total
along side *every* name on the list, so that you'll have duplicate data, but you wouldn't need to create a separate list of names, and you'll be sure to display the totals for all names in the list. =Sumif($A$1:$A$1000,A1,$B$1:$B$1000) And copy down. If you *need* to create a *unique* list of all names, with the totals along side, post back. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ltat42a" wrote in message ... Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Yes, I will need a *unique* list of names. I will have about 20-27 persons in this list. In the column to the right of each name is a number (representig hours). Each of the persons listed will have multiple entries in this list. The results that I'm looking for is "who" has "how many hours". If John Doe is in the list 50 times, each time he is listed, he has 2 hours. My result will be "John Doe" = 100hrs. This will repeat for each person in the list. Does this help?? Thanx Ragdyer Wrote: Since you say that the list is extensive, you could simply return the total along side *every* name on the list, so that you'll have duplicate data, but you wouldn't need to create a separate list of names, and you'll be sure to display the totals for all names in the list. =Sumif($A$1:$A$1000,A1,$B$1:$B$1000) And copy down. If you *need* to create a *unique* list of all names, with the totals along side, post back. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ltat42a" wrote in message ... Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Say your list of names is in A1 to A100,
and the hours are in B1 to B100. In C1, enter =A1 In C2, enter this *array* formula: =IF(ISERROR(MATCH(0,COUNTIF(C$1:C1,$A$1:$A$100&"") ,0)),"",INDEX(IF(ISBLANK($ A$1:$A$100),"",A$1:$A$100),MATCH(0,COUNTIF(C$1:C1, $A$1:$A$100&""),0))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. *After* committing the formula in C2 using *CSE*, drag down to copy as many rows as you anticipate you'll need to display all your unique names. THEN, in D1, enter this formula: =IF(C1<"",SUMIF($A$1:$A$100,C1,$B$1:$B$100),"") And drag down to copy as far as you have formulas (names) in Column C. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ltat42a" wrote in message ... Yes, I will need a *unique* list of names. I will have about 20-27 persons in this list. In the column to the right of each name is a number (representig hours). Each of the persons listed will have multiple entries in this list. The results that I'm looking for is "who" has "how many hours". If John Doe is in the list 50 times, each time he is listed, he has 2 hours. My result will be "John Doe" = 100hrs. This will repeat for each person in the list. Does this help?? Thanx Ragdyer Wrote: Since you say that the list is extensive, you could simply return the total along side *every* name on the list, so that you'll have duplicate data, but you wouldn't need to create a separate list of names, and you'll be sure to display the totals for all names in the list. =Sumif($A$1:$A$1000,A1,$B$1:$B$1000) And copy down. If you *need* to create a *unique* list of all names, with the totals along side, post back. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Ltat42a" wrote in message ... Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Have you ruled out Pivot Tables?
A Pivot Table would automatically list each unique name and present the total values for each name. Is that something you'd be interested in? *********** Regards, Ron XL2002, WinXP "Ltat42a" wrote: Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
No, I haven't ruled them out. I haven't used pivot tables before. Yes...I am interested. Thanx Ron Coderre Wrote: Have you ruled out Pivot Tables? A Pivot Table would automatically list each unique name and present the total values for each name. Is that something you'd be interested in? *********** Regards, Ron XL2002, WinXP "Ltat42a" wrote: Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
See http://www.contextures.com/xlPivot01.html
and http://www.peltierteh.com/Excel/Pivots/pivottables.htm for some good intros to pivot tables. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "Ltat42a" wrote in message ... No, I haven't ruled them out. I haven't used pivot tables before. Yes...I am interested. Thanx Ron Coderre Wrote: Have you ruled out Pivot Tables? A Pivot Table would automatically list each unique name and present the total values for each name. Is that something you'd be interested in? *********** Regards, Ron XL2002, WinXP "Ltat42a" wrote: Hi All, Can Excel count data that is in two columns? Example, I have a column of people's names. In the next column, I have a number. Column A Coulmn B John Doe 10 Jane Doe 8 Mary Doe 8 John Doe 8 John Doe 8 What I'm looking for is a way to count the number of "John Doe's" and the number to the right of his name. Then the same for Mary Doe, Jane Doe, etc...etc...etc... In the end, I'll have John Doe 26, Jane Doe 8, Mary Doe 8, etc...etc... This listing will be extensive - several hundred rows long. I'm trying to summarize each instance of John Doe & all his numbers and so forth with everyone's name that is in the list. Is this possible?? Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Ltat42a Here's a jumpstart on the Pivot Table process: First, make sure your data table has column titles (EmpName, Amount, etc) From the Excel main menu: <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the EmpName field here DATA: Drag the Amount field here If it doesn't list as Sum of Amount...dbl-click it and set it to Sum. Click [OK] Select where you want the Pivot Table...and you're done! That will list each EmpName and the sum of their respective amounts. To refresh the Pivot Table, just right click it and select Refresh Data To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted. I hope that helps. Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Thanx Ron...I think that will work much better than trying to use sums & counts formulas for each person. The only thing I need to figure out is, the list of names and hours will be constantly growing. Once I make the pivot table, do I have to re-make it again if I add to the data? The pivot table I made resides on a different worksheet than the data. The data worksheet will continue to grow with names and hours on a daily or weekly basis - all year long. Jim Ron Coderre Wrote: Ltat42a Here's a jumpstart on the Pivot Table process: First, make sure your data table has column titles (EmpName, Amount, etc) From the Excel main menu: <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the EmpName field here DATA: Drag the Amount field here If it doesn't list as Sum of Amount...dbl-click it and set it to Sum. Click [OK] Select where you want the Pivot Table...and you're done! That will list each EmpName and the sum of their respective amounts. To refresh the Pivot Table, just right click it and select Refresh Data To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted. I hope that helps. Regards, Ron -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to count data in 2 columns
Thanx Ron...I think that will work much better than trying to use sums & counts formulas for each person. The only thing I need to figure out is, the list of names and hours will be constantly growing. Once I make the pivot table, do I have to re-make it again if I add to the data? The pivot table I made resides on a different worksheet than the data. The data worksheet will continue to grow with names and hours on a daily or weekly basis - all year long. Jim Ron Coderre Wrote: Ltat42a Here's a jumpstart on the Pivot Table process: First, make sure your data table has column titles (EmpName, Amount, etc) From the Excel main menu: <Data<Pivot Table Use: Excel Select your data Click the [Layout] button ROW: Drag the EmpName field here DATA: Drag the Amount field here If it doesn't list as Sum of Amount...dbl-click it and set it to Sum. Click [OK] Select where you want the Pivot Table...and you're done! That will list each EmpName and the sum of their respective amounts. To refresh the Pivot Table, just right click it and select Refresh Data To learn about the other options Pivot Tables offer, check the links that Bob Phillips posted. I hope that helps. Regards, Ron -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Only count columns if the column next to it is not blank | Excel Discussion (Misc queries) | |||
| MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
| how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
| How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
| How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
