Counting names

J

johnfli

I have a workbook with several worksheets in it.

Each worksheet layout is the same.
For example:

Column A is for a Username
Column B is for the User City
Column C is for the user State


What I would like to do is have another worksheet that will look at all the
other worksheets and could how many times the User State of California
appears across all of the worksheets.
And all the times the city of LA
etc, etc, etc


How do I do that?

thanks in advance.
 
D

Dave Peterson

You could use a formula with a reference to each sheet you need:

=countif(sheet1!c:c,"california")+countif(sheet2!c:c,"california")+.....


But I think I'd consider putting all the data onto one sheet. Then I could use
lots of summarizing techniques--sorting and data|subtotals or even
data|pivottable...
 
P

Paul Falla

Dear John

I would use a pivot table based on multiple consolidation
ranges. To do this open the pivot table wizard (Data
menu/Pivot Table and Pivot Chart Report). When the wizard
starts, choose multiple consolidation ranges, then choose
Create a single page field for me. You will then be
promted for the ranges that contain your data. I
personally would choose the entire columns that your data
is in as this will enable you to refresh the pivot table
when new data is added to the worksheets, rather than
having to reset the consolidation ranges each time. After
that is done, you then need to decide if yor pivot table
goes on a new sheet of it's own, or on another existing
sheet.

Hope this helps

Paul Falla
 
D

Dave Peterson

That works pretty nice. (I've never used multiple sheets/ranges in my
pivottable.)
 
Top