sum of multiple IF statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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.
 
Thanks Macropod, this seems a possible solution, but I'm not familiar with
SUMIF and how it would apply in this circumstance.

macropod said:
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 said:
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
Click to expand...
Click to expand...
 
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.
 
Snap said:
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.
Click to expand...

=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))
 
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 said:
Thanks Macropod, this seems a possible solution, but I'm not familiar with
SUMIF and how it would apply in this circumstance.

macropod said:
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 said:
I have a formula (thanks to others) that provides a 1 or a blank
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top