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

Take a look at the SumIf and/or CountIf functions.

Good Luck.

"Anto111" wrote:

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

This can be achieved with a helper cell, in L7

=AND(B7="C Def")*AND(ISNUMBER(K7))

This will return 1 - sum the 1s and use that as the divisor in your other
formula to get the average

I am sure that it can be done in one cell without the aid of a helper but
it's late in the day and I want to go home

edvwvw



Anto111 wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

Hi,

I have tried, im just not sure how to formulate the sum, Im still quite
novice on Excel.

Cheers for your help,

;-)

"ND Pard" wrote:

Take a look at the SumIf and/or CountIf functions.

Good Luck.

"Anto111" wrote:

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

=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

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

Regards,
Stefi

€žAnto111€� ezt Ã*rta:

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

Hi Stefi,

many thanks. im getting close with that but that is dividing by all cells in
k7:k34, I only want to divide by the cells in k7:k34 that correspond to cells
in b7:b34 containing "C Def".

Really appreciate your time on this guys,

Thanks,

Ant

"Stefi" wrote:

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

Regards,
Stefi

€žAnto111€� ezt Ã*rta:

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

Like ND Pard said, I would have thought it would be more of a COUNTIF
function as I just need to divide by the number of cells that Contain "C Def"
in B7:B34 returning a value greater than zero in their corresponding cell in
K7:K34.

I'm sorry, I'm hopeless at putting this into action though!


"Anto111" wrote:

Hi Stefi,

many thanks. im getting close with that but that is dividing by all cells in
k7:k34, I only want to divide by the cells in k7:k34 that correspond to cells
in b7:b34 containing "C Def".

Really appreciate your time on this guys,

Thanks,

Ant

"Stefi" wrote:

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

Regards,
Stefi

€žAnto111€� ezt Ã*rta:

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

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

Regards,
Stefi


€žAnto111€� ezt Ã*rta:

Hi Stefi,

many thanks. im getting close with that but that is dividing by all cells in
k7:k34, I only want to divide by the cells in k7:k34 that correspond to cells
in b7:b34 containing "C Def".

Really appreciate your time on this guys,

Thanks,

Ant

"Stefi" wrote:

=SUMPRODUCT(--(B7:B34="C DEF"),K7:K34)/=SUMPRODUCT(--(K9:K170),--(B9:B17="C Def"))


Regards,
Stefi

€žAnto111€� ezt Ã*rta:

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