Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi
Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
You don't even need a formula for this one. Just a Pivot table.
See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Assuming your customer names in col F. One way is to use this in col G and
copied down. =COUNTIF($F$2:$F$22,F2) Sort or Then use match to find the row in that column and find the name it applies to. =INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi Gary
Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi Don
Many thanks for your reply, and I appreciate your help, but I have to confess - your reply made no sense to me whatsover. Sorry :( "Don Guillett" wrote: Assuming your customer names in col F. One way is to use this in col G and copied down. =COUNTIF($F$2:$F$22,F2) Sort or Then use match to find the row in that column and find the name it applies to. =INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Let's walk thru it step-by-step:
1. highlight cells A1 thru A13 (my example) 2. pull-down: Data PivotTable Next Next Layout You will now see 4 panes: Page, Row, Column, and Data Drag the Animal button into the Row pane and then also into the Data pane (the Data pane will show: Count of Animal) Then click OK Then click Finish Update this post if you experience any problems. -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Gary Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi Gary
You must be using a different version of Excel to me as there is no "PivotTable" option under the data tab. The only place I can find such an option is under the "Insert" tab, and then I do not get the next/next/layout options you speak of. I get asked such questions as whether I want the data on another spreadsheet, or the existing one. I did state in my original post that I was using Outllok 2007. Is this the version you are working from in your instructions? "Gary''s Student" wrote: Let's walk thru it step-by-step: 1. highlight cells A1 thru A13 (my example) 2. pull-down: Data PivotTable Next Next Layout You will now see 4 panes: Page, Row, Column, and Data Drag the Animal button into the Row pane and then also into the Data pane (the Data pane will show: Count of Animal) Then click OK Then click Finish Update this post if you experience any problems. -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Gary Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi Don
You don't explain where I put the "=COUNTIF($F$2:$F$22,D2)" forumula! What do you mean by "Copied Down"? "Don Guillett" wrote: A simpler way. Make a list of your clients in col D and use this copied down. Then sort. =COUNTIF($F$2:$F$22,D2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Don Many thanks for your reply, and I appreciate your help, but I have to confess - your reply made no sense to me whatsover. Sorry :( "Don Guillett" wrote: Assuming your customer names in col F. One way is to use this in col G and copied down. =COUNTIF($F$2:$F$22,F2) Sort or Then use match to find the row in that column and find the name it applies to. =INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
"Popey" wrote: Hi Gary You must be using a different version of Excel to me as there is no "PivotTable" option under the data tab. The only place I can find such an option is under the "Insert" tab, and then I do not get the next/next/layout options you speak of. I get asked such questions as whether I want the data on another spreadsheet, or the existing one. I did state in my original post that I was using Excel 2007. Is this the version you are working from in your instructions? "Gary''s Student" wrote: Let's walk thru it step-by-step: 1. highlight cells A1 thru A13 (my example) 2. pull-down: Data PivotTable Next Next Layout You will now see 4 panes: Page, Row, Column, and Data Drag the Animal button into the Row pane and then also into the Data pane (the Data pane will show: Count of Animal) Then click OK Then click Finish Update this post if you experience any problems. -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Gary Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Further tio my last reply;
Where I stated: "I did state in my original post that I was using Outllok 2007. Is this the version you are working from in your instructions?" I did mean Excel 2007. Sorry. "Popey" wrote: Hi Gary You must be using a different version of Excel to me as there is no "PivotTable" option under the data tab. The only place I can find such an option is under the "Insert" tab, and then I do not get the next/next/layout options you speak of. I get asked such questions as whether I want the data on another spreadsheet, or the existing one. I did state in my original post that I was using Outllok 2007. Is this the version you are working from in your instructions? "Gary''s Student" wrote: Let's walk thru it step-by-step: 1. highlight cells A1 thru A13 (my example) 2. pull-down: Data PivotTable Next Next Layout You will now see 4 panes: Page, Row, Column, and Data Drag the Animal button into the Row pane and then also into the Data pane (the Data pane will show: Count of Animal) Then click OK Then click Finish Update this post if you experience any problems. -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Gary Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Put the formula in any blank column that you desire. Then use the grab
handle and drag down. The grab handle can be had by going to the bottom right of the cell with the formula where there is a small square. Grab this with your mouse and drag down the column to the end of where your list of names is. Got it? -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Don You don't explain where I put the "=COUNTIF($F$2:$F$22,D2)" forumula! What do you mean by "Copied Down"? "Don Guillett" wrote: A simpler way. Make a list of your clients in col D and use this copied down. Then sort. =COUNTIF($F$2:$F$22,D2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Don Many thanks for your reply, and I appreciate your help, but I have to confess - your reply made no sense to me whatsover. Sorry :( "Don Guillett" wrote: Assuming your customer names in col F. One way is to use this in col G and copied down. =COUNTIF($F$2:$F$22,F2) Sort or Then use match to find the row in that column and find the name it applies to. =INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Got it!
In 2007 there is no field called "Data". The four boxes are labelled "Report Filter", "Row Labels", Column Labels", and "Values". I tinkered with them going by your instructions and finally managed to 'translate' what you were syaing into 2007 language Many thanks. "Gary''s Student" wrote: Let's walk thru it step-by-step: 1. highlight cells A1 thru A13 (my example) 2. pull-down: Data PivotTable Next Next Layout You will now see 4 panes: Page, Row, Column, and Data Drag the Animal button into the Row pane and then also into the Data pane (the Data pane will show: Count of Animal) Then click OK Then click Finish Update this post if you experience any problems. -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Gary Many thanks for the reply, but looking at the instructions for creating pivot data, my brain exploded out of my ears - it looks very complicated. All the instructions seem to be aimed at multiple columns of data. The example you gave (animals) looks just what I am looking for, but I can't see how it would be created. "Gary''s Student" wrote: You don't even need a formula for this one. Just a Pivot table. See: http://peltiertech.com/Excel/Pivots/pivotstart.htm for example if column A had: Animal mouse mouse cat cat cat dog cat cat cat cat dog dog the the pivot table would be: Count of Animal Animal Total cat 7 dog 3 mouse 2 Grand Total 12 -- Gary''s Student - gsnu200746 "Popey" wrote: Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Occurences In A Column
Hi Don
I do not know why, but I just couldn't get your formula to work. However, I eventually got what I wanted by using the Pivot Tabel idea from 'Gary"s Student'. Many thanks anyway for taking the time and effort to help. It was much appreciated. Thank you. "Don Guillett" wrote: Put the formula in any blank column that you desire. Then use the grab handle and drag down. The grab handle can be had by going to the bottom right of the cell with the formula where there is a small square. Grab this with your mouse and drag down the column to the end of where your list of names is. Got it? -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Don You don't explain where I put the "=COUNTIF($F$2:$F$22,D2)" forumula! What do you mean by "Copied Down"? "Don Guillett" wrote: A simpler way. Make a list of your clients in col D and use this copied down. Then sort. =COUNTIF($F$2:$F$22,D2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Don Many thanks for your reply, and I appreciate your help, but I have to confess - your reply made no sense to me whatsover. Sorry :( "Don Guillett" wrote: Assuming your customer names in col F. One way is to use this in col G and copied down. =COUNTIF($F$2:$F$22,F2) Sort or Then use match to find the row in that column and find the name it applies to. =INDEX(F2:F22,MATCH(LARGE(G2:G22,1),G2:G22,0)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Popey" wrote in message ... Hi Here's one for you Excel forumla wizards: Using Excel 2007 - I have a list of customer names in Column C of a customer database I have created, some names are repeated as they are frequent customers. Is there a (hopefully simple) formula that would automatically search column C and tell me how many times each customer has appeared in that column, thereby allowing me to readily see which customers return the most? Very many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Occurences Formulas | Excel Worksheet Functions | |||
Counting events since last occurences | Excel Worksheet Functions | |||
Counting the occurences | Excel Worksheet Functions | |||
Counting Occurences of Hours of the Day | Excel Discussion (Misc queries) | |||
Counting Occurences | Excel Discussion (Misc queries) |