formula question

[ML]

Hi.
I'm working on a spreadsheet of soccer fixtures and results for a school
project.
I've done formulas to result in games won, games played, points etc.
What I'm having problem with is the draw scores.
Say
Arsenal 2 v 2 Chelsea
MUnited 1 v 1 NCUnited
AVilla v Liverpool

The formula I used is
=IF(B1=D1, 1, 0) + IF(B2=D2, 1, 0) + IF(B3=D3, 1, 0)
but if you haven't entered a score yet like for the 3rd row and the cells
are empty it counts it as 1 as they are still equal.
please help
Thanks

 

[Sandy Mann]

Try:

=IF(and(B1=D1,B1<>""), 1, 0) + IF(and(B2=D2,B2<>""), 1, 0) +
IF(and(B3=D3,B3<>""), 1, 0)


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

especially if you have more than 3 results you might want to use

=SUMPRODUCT(--(B1:B3=D13),--(B1:B3<>""))

 

[T. Valko]

Try this:

=SUMPRODUCT(--(B1:B3<>""),--(D13<>""),--(B1:B3=D13))

Biff

 

[ML]

Thanks everyone.
The =IF(AND(..... worked a treat
M