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

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

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

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

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

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

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

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

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

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