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
hanklm

membership
Create an account to see this answer
Signing up is free. No credit card required.
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.
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