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

If image link works, you'll see what I've described above

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

|improve this answer|||||
  • Thank you to those who gave responses...the suggestion of removing the quote marks worked and now I feel like a fool as the fix was so simple. Again...thank you – Andrew Battley May 1 '18 at 21:31
  • Your formula is an unnecessary elaboration of =F4=11. – AFH May 1 '18 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.