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.

 

[sunstarer]

I wonder if this might help. See attached.

 

[hrayani]

It's workable if you could drop your file on a sharing site and provide a link here

If that's not workable then please answer few questions below.

1) What if range (A3:AC3) all have "w"
2) What if range (A3:AC3) all have blanks
3) What if range (A3:AC3) some w, some blanks

Just let me know you desired answers for all 3 scenarios

Further what formula are you using in your range (A3:AC3) ???

 

[sunstarer]

=COUNTIF(B3:AC3,"w") is what I'm using to count 'w's at the moment, which ignores blank spaces where there none. If there are no 'w's I need the Wins cell to say no wins, or '0' wins, specifically.

1) What if range (A3:AC3) all have "w" -- My current formula works just fine in that case, counting all the 'w's.
2) What if range (A3:AC3) all have blanks -- Then I need to count the lack of 'w's and report a '0' in the wins column.
3) What if range (A3:AC3) some w, some blanks -- This is exactly my case and the simple formula I've been using works as long as there is a single 'w' to be counted. If there are none, it fails to report '0'. That's what would I prefer, to see it report '0' when there are are no 'w's.

 

[sunstarer]

Once again, many thanks for your help people. It is much appreciated.
I'll check in again in the morning.

 

[Michael M]

Try this one then

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

 

[hrayani]

maybe this

=IF(COLUMNS(A3:AC3)=COUNTIF(A3:AC3,""),0,COUNTIF(A3:AC3,"w"))

 

[hrayani]

Try this one then

you got there first Micheal

And yes with an easier approach

 

[sunstarer]

So I should be sleeping but plugged the suggested changes into place and still no go.
If you are still game, I'm going to upload the file itself, it has to be easier that way.
I strongly suggest you do not download any excel file but open them only in google docs.
Link below. Thanks so much for your time, it is not lost on me and is really generous of you.

game count.xlsx

drive.google.com

 

[Michael M]

Ok, try it this way

Book1
	AI	AJ	AK	AL
3	Brian	5		D
4	Chris	6	3	50.00
5	ChuckFann	3	1	33.33
6	Chuck/Cmsh	25	5	20.00
Sheet1

Cell Formulas
Range	Formula
AI3:AI6	AI3	=A3
AJ3:AJ6	AJ3	=COUNTA(B3:AC3)
AK3:AK5	AK3	=IF(COUNTIF(B3:AC3,"w")=0,0,COUNTIF(B3:AC3,"w"))
AL3:AL6	AL3	=IF(OR(AJ3=0,AK3=0),"D",AK3/AJ3*100)
AK6	AK6	=COUNTIF(B6:AC6,"w")

Named Ranges
Name	Refers To	Cells
Print_Area	=Sheet1!$A$1:$AL$39	AI3:AL6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AJ2:AJ43	Cell Value	<$AH$8	text	NO
AJ2:AJ43	Cell Value	>=$AH$8	text	NO
AL2:AL54	Cell Value	top 1% values	text	NO

 

[sunstarer]

Thank you very much for that. There remains the issue that cell AK3 does not show a zero though, which is the only thing I need to happen that doesn't already. Brian had no wins this round and I'd like to be able to see that at a glance.