jana
asked on
Sum columns based on two values in Excel 2010
We are trying to SUM three (3) column and calculate its averaged but basing on the "Type" and "Year" that the line pertains to.
Included is an excel with what we are trying to do.
There are 3 columns with values (column Value1, Value2 and Value3).
We want to SUM and COUNT the columns "Value" based on the column "Type" and the column "Year".
Afterward get its average per value based on the year 2014.
In the excel we try to use SUMIF with COUNTA then tried to use SUMIFS, but can't seem to get the result we want.
The attached excel, in the columns N, O and P shows the value that the formula has to calculate (we placed it manually for comparison purposes).
Included is an excel with what we are trying to do.
There are 3 columns with values (column Value1, Value2 and Value3).
We want to SUM and COUNT the columns "Value" based on the column "Type" and the column "Year".
Afterward get its average per value based on the year 2014.
In the excel we try to use SUMIF with COUNTA then tried to use SUMIFS, but can't seem to get the result we want.
The attached excel, in the columns N, O and P shows the value that the formula has to calculate (we placed it manually for comparison purposes).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Thankx a lot! It worked!
We did find this function but tried to understand it and just couldn't get it running. Can you give a brief explaination (using your exact line below):
=AVERAGEIFS($H$14:$H$39,$D $14:$D$39, H$2,$E$14: $E$39,$G6)
We did find this function but tried to understand it and just couldn't get it running. Can you give a brief explaination (using your exact line below):
=AVERAGEIFS($H$14:$H$39,$D
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why not use a PivotTable?
I am having problems attaching files, but here's a download link for an example with two PivotTables
Average with PivotTables
I am having problems attaching files, but here's a download link for an example with two PivotTables
Average with PivotTables
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx!
ASKER
Summ-trying-read-Type.xlsx