| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum of multiple IF statements
I have a formula (thanks to others) that provides a 1 or a blank dependant on
data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
=IF(K3=M3,1,0)+IF(K5=M5,1,0)
"Snap" wrote: I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Hi Snap,
If you simply want to add up the column of 1s, a standard SUM formula will do. If you want to add up the 1s that correspond with a given team, then you need SUMIF, as in: =SUMIF(TeamNames,MyTeam,TeamScores) To tally the 1s, you can also use SUMIF, as in: =SUMIF(TeamScores,1,TeamScores) Cheers -- macropod [MVP - Microsoft Word] "Snap" wrote in message ... I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Hi!
Change your fomulas from: =IF(K3="","",IF(K3=M3,"1","")) To: =IF(K3="","",IF(K3=M3,1,"")) Enclosing numbers in quotes "1" converts the number to TEXT and can lead to problems. Why not just use a SUM function to sum the results of the 2 formulas? =SUM(P3,P5) =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) Using the syntax above will return an error if either of the condtionals return "". Biff "Snap" wrote in message ... I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Thanks Lance, but this formula counts blank cells eg if K3 and M3 are blank
(match not yet played, say) then a result of 1 is given. "LanceB" wrote: =IF(K3=M3,1,0)+IF(K5=M5,1,0) "Snap" wrote: I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Thanks Macropod, this seems a possible solution, but I'm not familiar with
SUMIF and how it would apply in this circumstance. "macropod" wrote: Hi Snap, If you simply want to add up the column of 1s, a standard SUM formula will do. If you want to add up the 1s that correspond with a given team, then you need SUMIF, as in: =SUMIF(TeamNames,MyTeam,TeamScores) To tally the 1s, you can also use SUMIF, as in: =SUMIF(TeamScores,1,TeamScores) Cheers -- macropod [MVP - Microsoft Word] "Snap" wrote in message ... I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Thanks Biff, I'm not screaming as much now! However, what I'm trying to do is
add the results of the 2 formula within the same cell as the formulae. "Biff" wrote: Hi! Change your fomulas from: =IF(K3="","",IF(K3=M3,"1","")) To: =IF(K3="","",IF(K3=M3,1,"")) Enclosing numbers in quotes "1" converts the number to TEXT and can lead to problems. Why not just use a SUM function to sum the results of the 2 formulas? =SUM(P3,P5) =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) Using the syntax above will return an error if either of the condtionals return "". Biff "Snap" wrote in message ... I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
"Snap" wrote in message
... Thanks Lance, but this formula counts blank cells eg if K3 and M3 are blank (match not yet played, say) then a result of 1 is given. "LanceB" wrote: =IF(K3=M3,1,0)+IF(K5=M5,1,0) =AND(K3=M3,K3<"",K5<"")+AND(K5=M5,K5<"",M5<"") If you want the answer to be blank until all the match results are entered, then you could use: =IF(OR(K3="",K5="",M3="",M5=""),"",(K3=M3)+(K5=M5) ) -- David Biddulph |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Hi Snap,
If all you want is a count of the 1s, 0s, 2s or whatever, Are you familiar with COUNTIF, then? You could use: =COUNTIF(A1:A100,1) where A1:A100 is the range containing the data you want to count. -- macropod [MVP - Microsoft Word] "Snap" wrote in message ... Thanks Macropod, this seems a possible solution, but I'm not familiar with SUMIF and how it would apply in this circumstance. "macropod" wrote: Hi Snap, If you simply want to add up the column of 1s, a standard SUM formula will do. If you want to add up the 1s that correspond with a given team, then you need SUMIF, as in: =SUMIF(TeamNames,MyTeam,TeamScores) To tally the 1s, you can also use SUMIF, as in: =SUMIF(TeamScores,1,TeamScores) Cheers -- macropod [MVP - Microsoft Word] "Snap" wrote in message ... I have a formula (thanks to others) that provides a 1 or a blank dependant on data input (the result of a match - tie=1). What I need now is a formula that will add the 1's to give a total number of ties for any particularteam. i'm trying to create a league table. The formula for each match is =IF(K3="","",IF(K3=M3,"1","")) I now want to apply this to another match eg =IF(K5="","",IF(K5=M5,"1","")) and add the two results to give an answer of 2. My logic came up with =SUM(IF(K3="","",IF(K3=M3,"1",""))+IF(K5="","",IF( K5=M5,"1",""))) but the computer tells me in its own way that I'm an idiot! Can anyone help stop me from screaming!! Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum of multiple IF statements
Thanks Dave - seems to work perfectly
- and Thanks to everyone else - I'm learning loads! "David Biddulph" wrote: "Snap" wrote in message ... Thanks Lance, but this formula counts blank cells eg if K3 and M3 are blank (match not yet played, say) then a result of 1 is given. "LanceB" wrote: =IF(K3=M3,1,0)+IF(K5=M5,1,0) =AND(K3=M3,K3<"",K5<"")+AND(K5=M5,K5<"",M5<"") If you want the answer to be blank until all the match results are entered, then you could use: =IF(OR(K3="",K5="",M3="",M5=""),"",(K3=M3)+(K5=M5) ) -- David Biddulph |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel MS Query multiple select statements | Excel Discussion (Misc queries) | |||
| Multiple If/Or Statements | Excel Worksheet Functions | |||
| combining IF and AND statements for multiple columns | Excel Discussion (Misc queries) | |||
| multiple nested IF statements | Excel Worksheet Functions | |||
| How to: Multiple "if" statements? | New Users to Excel | |||
Linear Mode
