sum of multiple IF statements

[Guest]

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

 

[Guest]

=IF(K3=M3,1,0)+IF(K5=M5,1,0)

 

[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

 

[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

 

[Guest]

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.

 

[Guest]

Thanks Macropod, this seems a possible solution, but I'm not familiar with
SUMIF and how it would apply in this circumstance.

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]

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

 

[Guest]

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.

 

[David Biddulph]

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.

=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))

 

[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]

Thanks Macropod, this seems a possible solution, but I'm not familiar with
SUMIF and how it would apply in this circumstance.

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]

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

 

[Guest]

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