# Need more than COUNTIF function, I think..

#### sunstarer

##### New Member
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?

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### Michael M

##### Well-known Member
So what do you actually want.....a formula that counts "w" AND 0 ??

#### sunstarer

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

#### Michael M

##### Well-known Member
Maybe this
Excel Formula:
``=IF(COUNTIF(A1:A10,"w")=0,COUNTIF(A1:A10,0),COUNTIF(A1:A10,"w"))``

#### hrayani

##### Well-known Member

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

##### New Member
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

##### New Member

@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

##### Well-known Member
@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

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

#### sunstarer

##### New Member
Just a little change in Micheal's formula

Excel 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

##### New Member
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.

Replies
6
Views
171
Replies
4
Views
210
Replies
2
Views
109
Replies
5
Views
121
Replies
2
Views
125