I am using a vlookup to assign points to a team in a sports league based on their result against a specific team. An example of formula I m using for that is; =IF(EXACT(E3,“Win”),VLOOKUP(B3,standings.$A$2:$F$48,5,0),VLOOKUP(B3,standings.$A$2:$F$48,6,0))
This works fine, I have a result for all 20 matches this team played. My question is how do I sum the results? My results are in G3-G19, yet =SUM(G3:G19) yields an answer of 0.
Thanks,
Mike
What kind of content (type Number or type Text) have the cell contents in coulmn E (wins) and F (losses)?
If you have text content there (may look like numbers!) the SUM() result will always be 0, because the SUM() function ignores any texts.
Thank you for the reply. All of the cells involved are formatted as a number. This includes column G where vlookup results are and the vlookup data itself.
The number format of a cell can’t and doesn’t afflict the result type (number or text) of a formula contained in that cell:
But cells that haven’t any actual number format, and instead the fake number format Text (@) won’t evaluate an entered format at all. They will not recognize it as a formula, but treat it as text (just another string).
Regard: The so-called number format Text is actually a directive not to interpret entered content as a number or a formula.
Lupp’s comment sent me in the correct direction. Even though my vlookup cells were formatted as number, the formula in them created text.
My original formula was looking at a team’s win percentage and then assigning win/loss bonus to it. An example of the formula is =IFS(D2<0.5,“0”,D2<0.667,“9”,D2>+0.667,“15”). Removing the quotes, so it looks like =IFS(D2<0.5,0,D2<0.667,9,D2>+0.667,15) fixed the issue.