Need more than COUNTIF function, I think..

sunstarer

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
So what do you actually want.....a formula that counts "w" AND 0 ??
 

sunstarer

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
I need to count 'w' and if there are none, then report '0'.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
Maybe this
Excel Formula:
=IF(COUNTIF(A1:A10,"w")=0,COUNTIF(A1:A10,0),COUNTIF(A1:A10,"w"))
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
985
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@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
Joined
Jul 23, 2010
Messages
985
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,127
Messages
5,546,075
Members
410,726
Latest member
TheSardOz
Top