Need more than COUNTIF function, I think..

[sunstarer]

With some of your help I built a spreadsheet that tallies wins in a game, that populates a cell with a win count. Works great. See below.

=COUNTIF(A1:A10,"w")

I'd like to add more functionality since when no one wins the cells are blank.
My goal is to have the column report a '0' if there are no wins, rather than show a blank cell and this simple formula can only count 'w' and provide a total.
Suggestions anyone?

Thanks very much in advance.

 

[Michael M]

So what do you actually want.....a formula that counts "w" AND 0 ??

 

[sunstarer]

I need to count 'w' and if there are none, then report '0'.

 

[Michael M]

Maybe this

=IF(COUNTIF(A1:A10,"w")=0,COUNTIF(A1:A10,0),COUNTIF(A1:A10,"w"))

 

[hrayani]

Hi Sunstarer

Your post # 1 states: since when no one wins the cells are blank.
Your post # 3 states: I need to count 'w' and if there are none, then report '0'.

In said case your formula and the one provided by Micheal will give same results

If you need to count zeros then you will have to set your formula in range A1:A10 to return zero rather than a blank cell if there are no wins

 

[sunstarer]

That looks good to my eye but does not work. Could be my fault. I did edit the cell names, nothing else. I do see a couple of red parentheses when I plug it in, don't those indicate a flaw? You can see how little I deal with excel. It's embarassing at times. Thank you for the assist very much. Let me play with this a bit more.

 

[sunstarer]

@hrayani, when there are no wins (w's), the cell is left blank. I'd like to check for blanks and calculate them too, besides count w's.

 

[hrayani]

@hrayani, when there are no wins (w's), the cell is left blank. I'd like to check for blanks and calculate them too, besides count w's.

Just a little change in Micheal's formula

=IF(COUNTIF(A1:A10,"w")=0,COUNTIF(A1:A10,""),COUNTIF(A1:A10,"w"))

 

[sunstarer]

Just a little change in Micheal's formula

=IF(COUNTIF(A1:A10,"w")=0,COUNTIF(A1:A10,""),COUNTIF(A1:A10,"w"))

I've modified it to be more specific to the cells in question and I get a strange result. It reports a large number of blanks rather than a '0'.

Here's my change, as you can see it only reference a different range of cells that my simple example I posted.

=IF(COUNTIF(B3:AC3,"w")=0,COUNTIF(B3:AC3,""),COUNTIF(B3:AC3,"w"))

 

[sunstarer]

I know this would be much easier for you folks if you had the spreadsheet but I'm quite certain that that is a bad notion on this forum. Thank you for your help, I'm sure it can be frustrating to try and work on something you can't see for yourself.