| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| League table | Excel Discussion (Misc queries) | |||
| league table | Excel Worksheet Functions | |||
| Basic IF function help needed | New Users to Excel | |||
| Multiply two colums; BASIC help needed | New Users to Excel | |||
| League Table | Excel Worksheet Functions | |||
Hybrid Mode
