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