Basic help needed using sumproduct to do a league table

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?
 
T

Tom Hutchins

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
 
C

Cougarric

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.
 
T

Tom Hutchins

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
 
T

Tom Hutchins

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
 
T

Tom Hutchins

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

Hutch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top