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

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)

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

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

regards, barry

=SUMIFS(E8:E36,C8:C36,"D",

.....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,

regards, barry

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

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

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.