Hi All,

I'm working with huge spreadsheets and I've sorted by a State/Prov column. Now I need a count of how many rows have each of the State/Provs, i.e. how many rows (customers) are in ON (Ontario).

I'm manually looking at the rows and subtracting the rows from the previous State/Prov, but there must be a better way.

If I put a blank row between each group, can I not do a count of all the rows above until the next blank row?

I'd really appreciate it if someone can figure this out and save me a lot of time. These spreadsheets are often so big that I have to divide them into three or four to fit into Excel (or use Access which I'm not as good at).

Thanks,

Michele

No need to put extra rows (that will only cause problems later).

Use the COUNTIF function. Assume your state/province is in column A starting with A2, and in Column B (starting with B2) put the unique States/Provinces (exactly as they are used in Column A). If you need to, use the Advanced filter to get the unique ones. Then in C2 put this formula:

=COUNTIF(A:A,B2)

Column A is the one to check, and B2 is the criteria by which to count.

Copy this down Column C as far as need (if you move cursor to lower right portion of C2, it will turn into a cross-hari, then double-click and it will fill down as far as you have unique values in Column B.

If this isn't what you had in mind, then perhaps post a sample of what you want.

Hi Michelle,

You might want to think about producing a pivot table this will summarise your data for you go to Data pivot table and pivotchart report and follow the steps through
If you are using Excel 2000 or higher once the wizard has finished you can drag fields directly on to your table from a toolbar
97 you have to do your table in the wizard and if you need to make changes just right click your table and choose wizard to get back into the wizard,
Shades method works perfect but i thought you might be interested in using a pivot

Dave

Good point, Dave. And another reason to avoid blank rows in the table, because that will cause problems with Pivot Tables as well.

Hi All,

What a quick response!

The COUNTIF function worked perfectly. I don't have time to learn pivot tables right now because I'm studying for my PMP exam next week, but it's something I've been meaning to do and sounds like I may need to.

Thanks again,

Michele

I really like the Pivot Tables. Don't be afraid to try them it just sounds more technical than they have to be. If you select a Cell in your data and click the "Data -> Pivot Table ...Report... " Menu Excel displays a wizard and defines your data area for the pivot table for you. As long as your data is in a list format, has headers and without spaces between any rows. Another reason to avoid blank rows. (there's and echo in here)

Something else to try is AutoFilter. Click a cell with a state value in it then select the Menu "Data->Filter->AutoFilter" This will roll up your data to show only rows with the matching state and in the status message (lower left) will give the count of the number of matches.

This is so usefull I customize my buttons to add the autofilter button (funnel with lightningbolt) and copy the autofilter toggle button from the data menu to the toolbar for quick access.


/

The COUNTIF approach is a good solution if you don't want to sort your rows, but since you said that you sorted by STATE/PROV, you could do it with less effort than COUNTIF and avoid building the list of all of the unique values, if you used Subtotals.

Click in your first cell, select from the menu bar DATA;SUBTOTALS. In the Box "At each change in:" select your field STATE/PROV. In the box "Use function:" select COUNT, and in the Box "Add subtotal to:" place a check mark in the box for STATE/PROV. Your spreadsheet now shows a count for each different STATE/PROV. If you don't want to see all of the detail, at the top left corner of the spreadsheet there will be 3 buttons. Click the 2 button.

TD