sum of multiple IF statements

[Snap]

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

[LanceB]

=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

[macropod]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
>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

[Snap]

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

[Snap]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Snap]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[David Biddulph]

"Snap" <[email protected]> wrote in message
news:[email protected]...
> 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

[macropod]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Snap]

Thanks Dave - seems to work perfectly
- and Thanks to everyone else - I'm learning loads!

"David Biddulph" wrote:

> "Snap" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>