[Optional]

Hoping someone can easily see what I am doing wrong here, as I've read the help files and a bunch of tutorial sites 14 times and am tearing my hair out.

I have a sheet with my soccer bet results in it.

Column B contains a list of Countries.
Column M contains a text label for the result. (WIN,LOSS.PUSH, ½P½W or ½P½L)

I'm trying to make a tally of Win-Loss-Push by country.

Here's the latest version of the formula, which doesn't work.


=SUM(IF($B$1:$B$1000="England",IF($M$1:$M$1000="WIN",1,IF($M$1:$M$1000="½P½W",0.5,0)),0))

I'm holding Ctrl+Shift when I press Enter.

All I get is a 0 or 1 result, depending on the country chosen.


I'm trying to say If the country is England and the outcome is WIN, count 1, and if the outcome is ½P½W count .5... and give me the sum of the results.

Any help would be greatly appreciated.

[djiddish98]

Try working with a pivot table.

I think if you just put a formula in column N with a conversion from outcome to number, regardless of country (your second two if statements), then run a pivot with the country as a row label and the numbered outcome as a value (make sure to switch to sum as opposed to count), you should get the desired result.

[That Foreign Guy]

Column N - "=if(Mx="Win",1,if(Mx="½P½W",0.5,0)) where x is the row number

Wins by country is then as follows

=Sumif(B:B,="England",N:N) where England is the country name.

That should do it.

[Optional]

Thanks guys.

Not sure it's the most elegant solution, but the second idea was so simple I feel like a dummy for not thinking of it myself. And it worked just as a wanted.

[Optional]

Just in case anyone else happens along with the same problem, I found the 'more elegant' solution I was looking for;

=SUM((B1:B1000="England")*(M1:M1000="WIN"))

Ctrl+Shift+Enter when entering it to make an array formula.

For some reason it won't work if I just try and specify the range as B:B, without row numbers. If anyone knows how I can make it work for an entire column, please let me know.