SUMProduct / Count

[Anto111]

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

 

[ND Pard]

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

Good Luck.

 

[Anto111]

Hi,

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

Cheers for your help,

;-)

 

[Bob Phillips]

=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)

 

[Stefi]

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

Regards,
Stefi

„Anto111†ezt írta:

 

[Anto111]

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]

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

Regards,
Stefi


„Anto111†ezt írta:

 

[Anto111]

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!