Count how many times a phrase comes up?

[Deplywrker]

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

 

[Pete_UK]

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

 

[Ashish Mathur]

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

 

[Shane Devenshire]

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