Lookup value in range, then if that value is something do a calculation.
 

Hi


I have a list of football results, i,e

A B C D

Man U 3 0 Bristol
Sheff U 6 1 West Ham
Wolves 1 2 WBA
Leeds 0 0 Arsenal
Man Utd 6 0 West Ham

On another sheet I want to log if a team has won.

A B
Team Wins
Arsenal in here I was going to put something along the lines
of if the team in A3 is in Sheet1 column A then if Sheet 2 Column B
Sheet2 Column C class then increase the count by 1. I was also then
going to see if Sheet 2 Column D contained Sheet1 column A and if so
see if Sheet2 C was greater than Sheet 2 B and add it?

Therefore, if you could help.

a) What formula would work this out please, I'm getting in a mess with
IF statements trying to look up a range for a single value.

b) Is there an easier way please? Ideally I'd also check for Draws
and losses as well,.

Cheers

Rich

Lookup value in range, then if that value is something do a calcul
 

One way would be like this where "Arsenal" is in sheet2!A2 and your results
data is in sheet1. This formula in sheet2!B2

=SUMPRODUCT(--(sheet1!A$2:A$100=A2),--(sheet1!B$2:B$100sheet1!C$2:C$100))+SUMPRODUCT(--(sheet1!D$2:D$100=A2),--(sheet1!C$2:C$100sheet1!B$2:B$100))

"Richhall" wrote:

Hi


I have a list of football results, i,e

A B C D

Man U 3 0 Bristol
Sheff U 6 1 West Ham
Wolves 1 2 WBA
Leeds 0 0 Arsenal
Man Utd 6 0 West Ham

On another sheet I want to log if a team has won.

A B
Team Wins
Arsenal in here I was going to put something along the lines
of if the team in A3 is in Sheet1 column A then if Sheet 2 Column B
Sheet2 Column C class then increase the count by 1. I was also then
going to see if Sheet 2 Column D contained Sheet1 column A and if so
see if Sheet2 C was greater than Sheet 2 B and add it?

Therefore, if you could help.

a) What formula would work this out please, I'm getting in a mess with
IF statements trying to look up a range for a single value.

b) Is there an easier way please? Ideally I'd also check for Draws
and losses as well,.

Cheers

Rich

Lookup value in range, then if that value is something do a calcul
 

Thanks, what does the -- do? WHy doesn't =IF(Sheet1!A$2:A$100=A2,1,0)
bring back 1 or 0 for me?

Lookup value in range, then if that value is something do a calcul
 

Thanks for your help, realised how to do it now using VLOOKUP. ta.