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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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