Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel Formula Question

Old 05-06-04, 06:54 AM
  #1  
DVD Talk Limited Edition
Thread Starter
 
Join Date: Sep 2000
Location: CT
Posts: 5,177
Excel Formula Question

so i have a big excel report, with about 16000 records (accounts). 'country' is one of the fields on the records. i'm looking for an easy way to find out how many accounts each country has. there's gotta be an easier way than filtering by country and going through each one to get the numbers... any ideas?

thanks.
mkdevo is offline  
Old 05-06-04, 12:19 PM
  #2  
DVD Talk Special Edition
 
Join Date: Jan 2001
Location: NY
Posts: 1,024
Any easy solution would be to use the Autofilter function under the Data --> Filter menu. You can then filter by each country and on the information bar on the bottom it will tell you how many records for each country you have. I'm not sure if this is what you tried already though. You could also try creating a pivot table.
bahgee is offline  
Old 05-06-04, 12:24 PM
  #3  
DVD Talk Hall of Fame
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,693
Pivottable would be my suggestion also, if you wanted it automated and not manual [the Filter thing is great, if you have say fewer than 20 'countries'].
tonyc3742 is offline  
Old 05-06-04, 01:26 PM
  #4  
Stealth Moderator
 
namja's Avatar
 
Join Date: Oct 1999
Location: High Definition
Posts: 24,967
Pivot Table is by far the BEST way to go. You can summarize by all countries and do a count in a single shot. To do this, click on Data -> Pivot Table, then select the entire data table (usually auto-selected, then choose all the default options (including create new worksheet). Once the Pivot Table worksheet opens choose:
Row Field = Country
Data Items = Name


If you want a quick count for one country, then you can use the COUNTIF function. Say you want to know how many accounts "Canada" has:
1) Go to a blank cell (be sure this cell is NOT in the same column as your 'Country' column).
2) Click on the Insert Function button (looks like fx) next to the formula bar.
3) Choose COUNTIF function.
4) For Range, select the column for Country.
5) For Criteria, type in "Canada".

VOILA.
namja is offline  

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive Advertising Cookie Policy Privacy Statement Terms of Service

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.