=AVERAGE(IF((B7:B34="C DEF")*(K7:K34<0),K7:K34))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Anto111" wrote in message
...
Hi,

I have a spreadsheet where I need to sum values based on positional
desigantion within a football team.

For example I need to sum values contained in K7:K34 only for players
designated as C Def in Column B7:B34.

I have been succesful with this using the formula:

=SUMPRODUCT(--(B7:B34="C DEF"),K7:K34)

What I need to do now though is get the average of this sum excluding zero
values and blank cells. I hope I have been clear enough!?

Many thanks in advance.

Ant