Basic help needed using sumproduct to do a league table

  • Thread starter Thread starter Cougarric
  • Start date Start date
C

Cougarric

I have created a fairly basic league table that uses SUMPRODUCT to extract
games won/drawn/lost from a list of fixtures.
This works fine. However, the fixtures that are blank get counted as draws.
How do I make sumproduct only read cells that have an entry in them?
 
Add another test to your SUMPRODUCT formula to only include records where the
fixture is not blank. If, for example, your fixtures are in A1:A100...

If you are using a double unary style SUMPRODUCT, you would add
--(LEN(A1:A100)>0),
to your SUMPRODUCT formula.

If you are multiplying terms in your formula, you would add
(LEN(A1:A100)>0)*
to your SUMPRODUCT formula.

As you can see, it would be easier to help you if you had posted your actual
formula.

Hope this helps,

Hutch
 
Hi. Sorry, yes I should have done that.

Anyway, here it is. I've tried to use your code but it hasn't worked for me
yet.

=SUMPRODUCT(($A$2:$A$65=J2)*($E$2:$E$65=$F$2:$F$65))

A2-A65 are the fixtures containing the team name (J2).
E2-E65 are the home scores; F2-F65 are the away ones.
 
Try this:
=IF(LEN(J2)>0,SUMPRODUCT(($A$2:$A$65=J2)*($E$2:$E$65=$F$2:$F$65)),0)

It looks to me like your SUMPRODUCT formula is fine, but you don't want to
call it if the fixture in column J is blank. Replace ,0 with ,"" if desired.

Hutch
 
Your formula is counting the number of times, for the team specified in J2,
that the home score equals the away score on the same row. Is that what you
intended?

Hutch
 
Are your blank fixtures and team names really empty cells, or do you have a
space in them?

Hutch
 
Back
Top