Basic help needed using sumproduct to do a league table
 

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?

Basic help needed using sumproduct to do a league table
 

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

"Cougarric" wrote:

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?

Basic help needed using sumproduct to do a league table
 

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.


"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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?

Basic help needed using sumproduct to do a league table
 

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

"Cougarric" wrote:

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.


"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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?

Basic help needed using sumproduct to do a league table
 

I've tried that, but it doesn't change the results.

"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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.


"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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?

Basic help needed using sumproduct to do a league table
 

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

"Cougarric" wrote:

I've tried that, but it doesn't change the results.

"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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.


"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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?

Basic help needed using sumproduct to do a league table
 

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

Hutch

"Cougarric" wrote:

I've tried that, but it doesn't change the results.

"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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.


"Tom Hutchins" wrote:

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

"Cougarric" wrote:

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?