Link to home
Create AccountLog in
Avatar of Jeremy-M
Jeremy-M

asked on

Calculating Senate Age by Party and Class (Election Year)

I have an excel 2010 workbook with the names, ages, parties affiliations and class.  There are 3 classes of Senators, Class 1 terms expire in 2018, Class 2 terms expire in 2014 and Class 3 terms expire in 2016.

The worksheet has columns for State, Senator, Party, Age and Class.

I want to calculate the average age of each Senator Class by party.  

The formula “=SUMIF(C8:C36,"D",E8:E36)” yields the sum of the ages of Senator whose party affiliation is “D” but how do sum the ages of members whose party is “D” and Class is “2014”.
 
How do I count only members whose Party is “D” and Class is “2014”?

I have attached my 2010 Excel Workbook.

Thanks,  

Jeremy
ASKER CERTIFIED SOLUTION
Avatar of hanklm
hanklm

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of nand633
nand633

You are asking for statistics by group. The best way is use PivotTable. It can caculate Sum/Averge/Count by group.

Click Insert and select PivotTable. In PivotTable Field List, pull the columns of "Class" and "Party" to the Row Lables and pull "Age" column to Values.

By defalt you will get the Sum or Count of the age. Click the "Sum of Age" to select Value Field Settings and change it to Averge.

Then you get the averge ages grouped by Party and Class.
Avatar of barry houdini
As Hanklm says you can use SUMIFS for the sum of the ages by class, e.g. if Class is in column F try

=SUMIFS(E8:E36,C8:C36,"D",F8:F36,2014)

.....but you said you were looking for an average age, so for that you can use AVERAGEIFS with exactly the same syntax, i.e.

=AVERAGEIFS(E8:E36,C8:C36,"D",F8:F36,2014)

regards, barry
Avatar of Jeremy-M

ASKER

Thanks to all who offered solutions.

I am accepting HankLM's solution because I used it and he was first.

I learned a lot from the other two experts who offered solutions and I thank you for that.

Jeremy