Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count how many times a phrase comes up?
I need to count how many times certain phrases come up from multiple
worksheets. Let me explain the problem more in detail. I have a worksheet for every day and each day has about 8-20 names/ numbers listed (ex. 00515 - name 1, 00515 - name2, 65487- name 3). Instead of me going through each day and counting how many times each one comes up, I was wondering if there is a way to have a formula on the last page to count how many times each phrase is lested from 31 worksheets. I have the list of all phrases on the 1st worksheet that has about 90 differ names. Conclusion: Is there a way to say look for whats listed in A1 on this worksheet and search all 31 worksheet tabs and tell me how many times it comes up? Thank You |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count how many times a phrase comes up?
Might be easier if you were to lay out your summary data like this:
Sheet1 Sheet2 Sheet3 etc Phrase1 x x x Phrase2 x x x Phrase3 x x x etc. because Excel is not very good for multi-sheet references. In this 2-d table you can get a count against each phrase and each sheet, and then just total across. An alternative is to have a 2-column table in each sheet using the same cells, and then you could sum from cell B163 (for example) for all sheets. You will need to give further details of cells/columns/sheetnames used etc if you want a specific formula. Hope this helps. Pete On Nov 12, 6:37*pm, Deplywrker wrote: I need to count how many times certain phrases come up from multiple worksheets. Let me explain the problem more in detail. I have a worksheet for every day and each day has about 8-20 names/ numbers listed (ex. 00515 - name 1, 00515 - name2, 65487- name 3). Instead of me going through each day and counting how many times each one comes up, I was wondering if there is a way to have a formula on the last page to count how many times each phrase is lested from 31 worksheets. I have the list of all phrases on the 1st worksheet that has about 90 differ names. Conclusion: Is there a way to say look for whats listed in A1 on this worksheet and search all 31 worksheet tabs and tell me how many times it comes up? Thank You |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count how many times a phrase comes up?
Hi,
The best way to solve this problem would be to collate all data into one sheet - the reason being that SUMIF() or COUNTIF() does not work with 3D references. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Deplywrker" wrote in message ... I need to count how many times certain phrases come up from multiple worksheets. Let me explain the problem more in detail. I have a worksheet for every day and each day has about 8-20 names/ numbers listed (ex. 00515 - name 1, 00515 - name2, 65487- name 3). Instead of me going through each day and counting how many times each one comes up, I was wondering if there is a way to have a formula on the last page to count how many times each phrase is lested from 31 worksheets. I have the list of all phrases on the 1st worksheet that has about 90 differ names. Conclusion: Is there a way to say look for whats listed in A1 on this worksheet and search all 31 worksheet tabs and tell me how many times it comes up? Thank You |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count how many times a phrase comes up?
Hi,
Let's take the two previous suggestions and create the formula to return the results for each sheet. Suppose that the sheet names run from B1:M1 and the phrases run from A2:A100 In cell B2 enter the following formula and then copy it to all the cells B2:M100 =COUNTIF(INDIRECT(B$1&"!A1:H100"),$A2) Change the range A1:H100 to whatever the range is where the phrase may be found. Now if you want the total you can add a sum column on the right or left. If this helps, please click the Yes button, Cheers, Shane Devenshire "Deplywrker" wrote: I need to count how many times certain phrases come up from multiple worksheets. Let me explain the problem more in detail. I have a worksheet for every day and each day has about 8-20 names/ numbers listed (ex. 00515 - name 1, 00515 - name2, 65487- name 3). Instead of me going through each day and counting how many times each one comes up, I was wondering if there is a way to have a formula on the last page to count how many times each phrase is lested from 31 worksheets. I have the list of all phrases on the 1st worksheet that has about 90 differ names. Conclusion: Is there a way to say look for whats listed in A1 on this worksheet and search all 31 worksheet tabs and tell me how many times it comes up? Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
Count Number of Times Q | Excel Worksheet Functions | |||
Count # of times a value is in another spreadsheet | Excel Discussion (Misc queries) | |||
Count how many times a certain value is in a certain range | Excel Discussion (Misc queries) | |||
Count # of times a Reference is used | Excel Discussion (Misc queries) |