Formula Help!

[Killer]

I have spreadsheet with the following:

F3 = $175

This formula
=SUMPRODUCT(--(C$51:AJ$51=B41),--(C41:AJ41>0))*F$3

Returns a result whoever has the highest would win $175 but now there might
be a second person involved based on a tie, if there is then the pot should
be split in half between the two people.

Winners names are located in C51: AJ51 & C52 : AJ52

Thanks!

 

[Bob Phillips]

How about

=$F$3/SUMPRODUCT(--(C$51:AJ$51=B41),--(C41:AJ41>0))

--
HTH

Bob

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

 

[Killer]

Hey Bob thanks for the help but that doesn't work.

=SUMPRODUCT(--(C$51:AJ$51=B41),--(C41:AJ41>0))*F$3

The formula above works only if a person names is in cell C$51:AJ$51 but
doesn’t take into count if a second person name is showing in cells C$52:AJ$52

If two people names are showing say S51 &S52 then the result should be a
split of the amount in cell F3 towards each person name in S51 &S52

I hope this more clearly.

Thanks for the Help!

 

[Killer]

Can anyone help me on this formula?

Hey Bob thanks for the help but that doesn't work.

=SUMPRODUCT(--(C$51:AJ$51=B41),--(C41:AJ41>0))*F$3

The formula above works only if a person names is in cell C$51:AJ$51 but
doesn’t take into count if a second person name is showing in cells C$52:AJ$52

If two people names are showing say S51 &S52 then the result should be a
split of the amount in cell F3 towards each person name in S51 &S52

I hope this more clearly.

Thanks for the Help!

 

[Bob Phillips]

=COUNTIF($C51:$AJ51,B41)/SUMPRODUCT((ISNUMBER(MATCH($C$51:$AJ$52,$B$41:$B$42,0)))*($C$41:$AJ$42>0))*F$3

will get the first name value

--
HTH

Bob

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

 

[Killer]

Hey Bob

Thanks for the help but it's still not working correctly....

I have uploaded the spreadsheet if you would kindly take look and see what
I'm trying achieve.

http://nghl.ca/Football_Pool.xls

On the master tab you’ll see the areas colored in yellow along the C51 &c52
are where the names will be listed based on what names are listed in theses
areas will determine the payouts to whom.
Cells AO8:AO47 is where the formula result should show…

If there’s a names in both cells then it should split the $175 (Cell F3) pot
between them giving each person $87.50 if only one name is showing them that
person should be given $175.

As you can see W51 & W52 has two names but with my formula it’s awarding
Doug the full amount when actually it should be a split pot between Doug and
Glen of $87.50

Again thanks for the help it’s much appreciated...