Counting lots of stuff in Excel 2010

ElJeffe

Okay, I have two columns I'm interested in. One has a zip code, one has either the text string "violent" or "non-violent". What I want is a tally of, for each zip code, how many times each string appears. So something like:

ZIP NON-VIOLENT VIOLENT
95608 37 42
95609 120 64

...and so on.

There are about 2500 rows per worksheet, and I'm going to have 12 worksheets, so counting manually is right out. I know I can sort by zip code, then manually hunt to the end of each run of a given zip and do a countif to count the Vs and NVs, but this is clunky (there are about 50 different zip codes per worksheet) and it seems like there should be a better way. I'd prefer not having to write any VB code, because I haven't done that in forever and don't feel like relearning the process. Anyone know of a good means of doing this with operators and/or clever sorts or anything?

Myiagros

I can probably help, I'm a bit lost on what you are trying to add up though. Is each zip a new one so column A would always be different and you want to know the NV and V value at the end of X number of zip codes?

Deebaser

Pivot tables.
Also, you might want to consolidate the twelve tables into a single data table.

ElJeffe

@Miyagros

If the table looked like this:

95608 violent
95608 violent
95608 non-violent
95810 violent
95810 non-violent
95810 violent
95810 violent
95821 violent
95821 non-violent
95823 violent

...then I would want to get data that looked like this:

ZIP         VIOLENT      NON-VIOLENT
95608          2                    1
95810          3                    1
95821          1                    1
95823          1                    0

I have considered merging the worksheets into one, and will do that if it saves time. I need to keep track of what came from where, but I can just throw another column in there to track the month. (The worksheets are broken up by month.)

edit: Also, it looks like pivot tables will be a good way to do this. I'm futzing about with them right now, and hopefully I can figure out how to properly wrangle those numbers.

Deebaser

That is still a job for pivot tables

Click the Insert ribbon
Click pivot table
insert pivot table on a seperate sheet
Drag zipcode to row label
Drag the header for violent/nonviolent to "Column Labels" and "values"
click on the column header in "values" and change the "Value Field Settings" to Count

Dr. Frenchenstein

deebaser has got it.

you could probably do it with subtotals too, but pivot tables are prettier.

ElJeffe

Got it! Thanks @Deebaser , you are my new forum best friend.

Gonmun

I use pivot tables on my monthly reports for order entry and it's a god send. As well, with the info you get from the table you can double-click on the specific amounts under each zip and it will give the break down of the cells for each on a separate tab. Really comes in handy.

schuss

I hate pivot tables in excel (because their implementation is really odd and finicky), but they're still incredi-awesome for shit like this.

Nija

I agree with the Pivot Tables.

I was doing something similar using Match and then Filtering out the N/A. Actually, I still do that. I need to update my spreadsheet. Thanks guys!

useless4

It's funny - I get paid to play with excel all day. pivot tables are the most logical way to do this yet I have spent the last ten minutes without Excel trying to use tables and countif statements to make it all complicated and fun. Now I have to get to work to try out my stupid complicated way of doing it! (god I need a better hobby then excel)

schuss

useless - get your hands on some Business intelligence tools (Cognos/Hyperion(Oracle)/etc.). It's like super-excel that does tons of neat stuff with data.

useless4

Have cognos and use it quite a bit just waiting for them to turn an instance on where i really need it