|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How to count data in 2 columns
Ltat42a If you base your Pivot Table on a Dynamic Range Name, it will automatically include any additions to the list when you refresh the Pivot Table. For really good instructions on how to create a Dynamic Range Name, see Debra Dalgleish's website: http://www.contextures.com/xlNames01.html#Dynamic Does that help? Regards, Ron (This may be a dbl-post...the first try bounced to an error screen) -- 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 |
How to count data in 2 columns
Hey Ron, I read through the tutorials about naming ranges and dynamic data source. The spreadsheet that I'm using is exported from a table query in Access. This spreadsheet has the employee names (Col A) and hours (Col B) listed. When I create the pivot table based on the range of names and hours, it works pretty good. If I add to this list - the pivot table does not update. Right now, in Col A & Col B, my list goes down to row 25. I will be adding to these columns through out the year. I may have as many as 300 rows - but only the two columns. I've tried & tried to get the pivot table to update, when I add rows to these two columns, the table does not update, I'm baffled. Any Suggestions? Thanx...Jim -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
How to count data in 2 columns
Jim You'll need to use a Dynamic Range Name, as described in my previous post. A Dynamic Range Name automatically resizes as the data area grows or shrinks. Follow the instructions at the Contextures website: http://www.contextures.com/xlNames01.html#Dynamic and post back if you have any questions. 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 |
How to count data in 2 columns
Here's a rundown of what I have: Excel 2003 In my spreadsheet, I have only one worksheet (tbl_hours_query) In this worksheet, A1 = my header name "Employee", B1 = "Hours". A2: A25 = all my names, B2:B25 = hours for these employees. I highligthed A1:A25, named the range "Employee" I highlighted B1:B25, named the range "Hours" Here's where I'm a lil baffled. I want to use a dynamic data source, says to Insert, Name, Define, then insert the offset formula which I do. Do I do this in the same worksheet as the data, or in a new worksheet? I use this for the "refers To:" =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),2) Note: I removed the "Data!" when I do this in the same worksheet as my list, I made a new worksheet, and inserted the worksheet name. If I do either of these then add in the pivot table, the pivot table does not update when I add names and hours to the list. I'm gonna get this, it will not defeat me!!!!!!!!!!! Thanx. -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
How to count data in 2 columns
Ok, Jim....I think we got it this time. On the list worksheet... <insert<name<define Names in workbook: rngPivotSource Refers to: =OFFSET($A$1,0,0,COUNTA(Data!$A:$A),2) That will create the Dynamic Range Name that you will use in the Pivot Table. (You don't need separate ranges named Employees or Hours for the Pivot Table. The one rngPivotSource range will suffice) I think you may have gotten that far ok....but, the confusion may be with the way Pivot Tables work. The reason the data displayed in a Pivot Table doesn't change unless you REFRESH the Pivot Table is this. Pivot Tables use a hidden, internal COPY of the source data. Doing that freezes the data set so can analyze a static data set, even if the source data is changing. Refreshing the Pivot Table replaces the internal data cache with the latest information. So, after pulling new data, deleting rows, or adding rows....refresh the Pivot Table to see the latest data. Does that help? 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 |
How to count data in 2 columns
Hey Ron....think I got it working. I think the mistake I was making was that I was creating the "Insert, Name, Define + offset formula" for both the employee column and the hours column. Every time I insert new names and hours, the pivot table was not updating. I deleted everything in "Insert...Names......" just inserted the "Employee name with the offset formula, changed the last numeral to "2" (2 columns), and it seems to be working. I made the pivot table appear in a new worksheet, added names and hours to the list, updated the pivot table and it seemed to update fine. Thanx for all the help (and patience!). It's definetly a nice little tool we'll be using for quite some time. Thanx again...Jim -- Ltat42a ------------------------------------------------------------------------ Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735 View this thread: http://www.excelforum.com/showthread...hreadid=546477 |
| All times are GMT +1. The time now is 08:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com