Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 12th 05, 08:59 PM
Cindy
 
Posts: n/a
Default Column A is Town, Column B is names. How can Excel add & tell how.

Column A is Town, Column B is names, Column C is how many in each household.
How can Excel add & tell me how many Smiths in each town and, if possible,
how many Smith families with 2 people in each town. Then I want to chart the
info. How many of each family name by town.

  #2   Report Post  
Old January 12th 05, 11:04 PM
Bernard Liengme
 
Posts: n/a
Default

Read Help for Pivot Table

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cindy" wrote in message
...
Column A is Town, Column B is names, Column C is how many in each
household.
How can Excel add & tell me how many Smiths in each town and, if possible,
how many Smith families with 2 people in each town. Then I want to chart
the
info. How many of each family name by town.



  #3   Report Post  
Old January 13th 05, 12:09 AM
John Mansfield
 
Posts: n/a
Default

Cindy,

Given that "Town" is in cell A1 and using this data as an example:

Town Names Members
Rochester Smith 4
Rochester Jones 3
St. Paul Smith 2
Minneapolis Franklin 4
Rochester Jamison 2
Minneapolis Jones 3
St. Paul Smith 2
St. Paul Jones 4
Rochester Barnes 3

Total Smith members = 8 (see formula below)

=SUMIF($B$2:$B$10,"Smith",$C$2:$C$10)

The number of Smith Families with 2 members = 2 (see formula below)

=SUMPRODUCT(($B$2:$B$10="Smith")*($C$2:$C$10=2))

The number of Smith families = 3 (see formula below)

=COUNTIF(B2:B10,"Smith")

You might want to look up the SUMPRODUCT, SUMIF, and COUNTIF functions in
Help for explanations.

What type of chart do you need to create?

----
Regards,
John Mansfield
http://www.pdbook.com

"Cindy" wrote:

Column A is Town, Column B is names, Column C is how many in each household.
How can Excel add & tell me how many Smiths in each town and, if possible,
how many Smith families with 2 people in each town. Then I want to chart the
info. How many of each family name by town.

  #4   Report Post  
Old January 13th 05, 07:27 PM
Cindy
 
Posts: n/a
Default

I want a column or bar chart and need the chart to include each town along
the left side and each name along the bottom and to automatically add how
many Smiths, Jones, etc. in each town. I am not sure if the formula that you
gave me, which includes =Smith, would accomplish that. I need Excel to be
smart enough to figure out that each different name would be a different line
on the chart. I need it to figure how many Smiths, how many Jamisons, Jones,
etc. Thank you so much for your help.

"John Mansfield" wrote:

Cindy,

Given that "Town" is in cell A1 and using this data as an example:

Town Names Members
Rochester Smith 4
Rochester Jones 3
St. Paul Smith 2
Minneapolis Franklin 4
Rochester Jamison 2
Minneapolis Jones 3
St. Paul Smith 2
St. Paul Jones 4
Rochester Barnes 3

Total Smith members = 8 (see formula below)

=SUMIF($B$2:$B$10,"Smith",$C$2:$C$10)

The number of Smith Families with 2 members = 2 (see formula below)

=SUMPRODUCT(($B$2:$B$10="Smith")*($C$2:$C$10=2))

The number of Smith families = 3 (see formula below)

=COUNTIF(B2:B10,"Smith")

You might want to look up the SUMPRODUCT, SUMIF, and COUNTIF functions in
Help for explanations.

What type of chart do you need to create?

----
Regards,
John Mansfield
http://www.pdbook.com

"Cindy" wrote:

Column A is Town, Column B is names, Column C is how many in each household.
How can Excel add & tell me how many Smiths in each town and, if possible,
how many Smith families with 2 people in each town. Then I want to chart the
info. How many of each family name by town.



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
Change the width of a single column in a column chart Dave Charts and Charting in Excel 2 December 13th 04 07:25 PM


All times are GMT +1. The time now is 12:22 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017