Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 29th 09, 03:34 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 1
Default summing specifc state abbreviations in a column

I have a list of addresses and would like to sum the number of addresses in
each state. There is a column with the 2 letter state abbreviation for each
address. How can I do this? I have never summed actual text in a column before

  #2   Report Post  
Old May 29th 09, 03:55 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,276
Default summing specifc state abbreviations in a column

hI,
I assume your addresses are in column A and the 2 letter state in Column B,
create a list of all the States posibles in column c and in column D enter (
I suppose your list starts in D1

=COUNTIF($b$1:$B$100,D1)

Copy formula down change the range to fit your needs

"Kathy S" wrote:

I have a list of addresses and would like to sum the number of addresses in
each state. There is a column with the 2 letter state abbreviation for each
address. How can I do this? I have never summed actual text in a column before

  #3   Report Post  
Old May 29th 09, 04:09 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default summing specifc state abbreviations in a column

Countif() is what you want to use.

Say your datalist is in A2 to K100, with the state in Column J.

You could just add another column to your datalist, and use this formula to
return the count of each state in each row:

In L2 enter:
=Countif(J$2:J$100,J2)

and copy down to L100.

This would of course return duplicate results for each row with the same
state.

OR

You could make a separate list of all 50 states, say in M1 to M50,
and then enter this formula in N1, and copy down:

=Countif(J$2:J$100,M1)


--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Kathy S" <Kathy wrote in message
...
I have a list of addresses and would like to sum the number of addresses in
each state. There is a column with the 2 letter state abbreviation for each
address. How can I do this? I have never summed actual text in a column
before




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting cell for state abbreviations LovesArt247 New Users to Excel 10 April 15th 09 03:38 AM
Validation for state abbreviations k1ngr Excel Discussion (Misc queries) 2 February 29th 08 10:09 PM
state abbreviations gls858 New Users to Excel 7 June 20th 07 09:34 PM
Converting State Names to State Abbreviations aznate Excel Discussion (Misc queries) 1 October 20th 06 06:52 AM
US State Abbreviations List DTTODGG New Users to Excel 1 November 16th 05 01:36 PM


All times are GMT +1. The time now is 09:36 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017