Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average Formula with Criteria
Hi, I have a spreadsheet that is used to average mechnical and electrical costs for a number of projects, split into years. Each project has a Work Area (m2) and a specific contractor (there are three different contractors that are responsible for the projects). At the bottom of the spreadsheet I have a number of average tables, one for each contractor. I currently average the Work Area by using an average function and manually selecting all the work areas for the specific contractor. However, when I add projects this becomes a bit of a hasle. Ideally I want a formula that says: Average the Work Area (m2) of all lines between D1 & D53 that have a column G value (contractor cell) of "PW Interiors" I hope this makes some sense, any help would be much appreciated.! Phil |
#2
|
|||
|
|||
couple of ways
=sumif(g1:g53,"PW Interiors",d1:d53)/countif(g1:g53,"PW Interiors") or =average(if(g1:g53="PW Interiors",d1:d53)) array entered (ctl+shift+enter) "PW11111" wrote in message ... Hi, I have a spreadsheet that is used to average mechnical and electrical costs for a number of projects, split into years. Each project has a Work Area (m2) and a specific contractor (there are three different contractors that are responsible for the projects). At the bottom of the spreadsheet I have a number of average tables, one for each contractor. I currently average the Work Area by using an average function and manually selecting all the work areas for the specific contractor. However, when I add projects this becomes a bit of a hasle. Ideally I want a formula that says: Average the Work Area (m2) of all lines between D1 & D53 that have a column G value (contractor cell) of "PW Interiors" I hope this makes some sense, any help would be much appreciated.! Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compounded formula using SUM & AVERAGE | Excel Worksheet Functions | |||
conditional formula - based on 2 separate criteria | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Formula Does Not Omit Criteria | Excel Worksheet Functions | |||
Formula to determine a future date based on criteria | Excel Worksheet Functions |