# Excel SUM function not working when I have used the IF function to gain results

I am using an Excel spreadsheet to automate the results of a table tennis tournament and see who proceeds to the next round.

I have a section of spreadsheet detailing the matches being played with a space for the match results. To the side I have separate section that accumulates players total scores and if they won or lost matches (who goes through is based on total wins). I have used the `IF` function to tell my accumulation section to look up each players result and to return a 1 if they won and a 0 if they did not win. The formula I used was `=IF(F4=11,"1","0")`. In this case cell `F4` is one of the match scores for Simon Westbrook. This part is working fine.

However, my Total column in the accumulation section has stopped working (again, if image link above has worked, you'll see where I mean).

Before I input the 1s and 0s for wins / losses manually and the total column had the `SUM` function of (as example) `=SUM(O4:Q4)`. Based on linked image, this previously would have returned a result of 2.

Since including the `IF` function to automate wins / losses, the `SUM` formula has stopped working. I have checked all cells are number. I have also dragged mouse across cells to see what comes up in that 'Sum' box at the bottom of excel sheet...it stayed at zero when I scrolled over my win / loss boxes.

I have also tried opening the formula toolbox to see if Excel can suggest a fix. Under the arguments within toolbox the formula shows {"1","0","1"} but with a result of 0.

Can anyone suggest an alternative formula to `SUM` when you're trying to total rows that are using the `IF` formula, or a way to tell Excel to recognise the numbers in the win / loss boxes?

First time user on here, so apologies for length of question and if not formatted entirely correctly.

• I can't follow that link from the firewall I'm behind, but I feel like the "Sumifs" command might be what you're looking for. – mauve May 1 '18 at 19:08
• Using the quotes force a string instead of a number, try using `=IF(F4=11,1,0)` – cybernetic.nomad May 1 '18 at 19:32
• We prefer that you not post images that represent only textual data (even spreadsheets).  But what’s worse is that your illustration doesn’t really illustrate anything.  Row 4 (columns `D:F` and `L:R`) contains the data (match scores) for Simon Westbrook.  Ho hum.  `F4` (Simon Westbrook’s score) is 11.  OK.  `=IF(F4=11,"1","0")` appears to evaluate to `1`.  OK, but where?  `O4`? `Q4`?  You’ve got a `SUM()` of a range of three cells that appear to contain `1`, `0` and `1`, … (Cont’d) – G-Man Says 'Reinstate Monica' May 2 '18 at 3:55
• (Cont’d) …  and the sum (in cell `R4`?) contains `0`. OK; that’s the issue.  What does your screenshot show or even clarify?  (And why do you have `N7` selected?)  … … … … … … … … …  P.S. What your picture does show is that you have `=SUM(J5+J6+J9)` in `N7`.  (1) This is equivalent to `=J5+J6+J9`.  You are calling `SUM()` with one argument.  (2) I notice that cells `J5`, `J6` and `J9` are all associated with “Richard Marsh”.  So you build the formulas in Column `N` manually based on which names are in which rows?  You should use `SUMIF` to add all the values (scores) that correspond to a name. – G-Man Says 'Reinstate Monica' May 2 '18 at 3:55
• @AFH: “Because it’s there” is not sufficient reason to inline an image.  Not all images need to be in-lined (see above). – G-Man Says 'Reinstate Monica' May 2 '18 at 3:55

As cybernetic.nomad says, quotes give you strings (text values).  Strings whose values look like numbers are weird — sometimes you can do arithmetic with them, and sometimes you can’t.  In particular, `SUM` seems not to work.
Unless you have a really really really good reason for using strings, you should just stick with numbers and say `=IF(F4=11, 1, 0)`.
• Your formula is an unnecessary elaboration of `=F4=11`. – AFH May 1 '18 at 21:34