Count occurrences and if zero then leave cell blank.... How???

[Losguapos1]

So here is my dilemma,

I have a sheet that has a calculated cell that counts the number of occurrences a given value occurs. The formula in that cell is =COUNTIF('Grade K-8'!B1:CR570,"1,51") I want to add to it so that if the count is "0" it will return a blank cell

I hope this is enough information, let me know if it isn't

George

[Jonmo1]

You pretty much have to do

=IF(COUNTIF(...)=0,"",COUNTIF(...))

[Losguapos1]

Thank you for the quick reply,

I am no excel head, what would the formula look like with my formula in it. Would i just substitute the ... for what is inside my parenthesis?

[Jonmo1]

Or a trick I recently learned...
I'm not familiar with MAC, but if it has the IFERROR function like XL2007+ on PC,

=IFERROR(1/(1/COUNTIF(...)),"")

[Jonmo1]

Would i just substitute the ... for what is inside my parenthesis?

That's right.

[Losguapos1]

THANKS a million Jonmo1

[Jonmo1]

You're welcome.

[Losguapos1]

one more question, i now need to update a ton of formulas


=IF(COUNTIF('Grade K-8'!B1:CR570,"1,51")=0,"",COUNTIF('Grade K-8'!B1:CR570,"1,51"))
=IF(COUNTIF('Grade K-8'!B1:CR570,"1,51")=0,"",COUNTIF('Grade K-8'!B1:CR570,"1,51"))
=IF(COUNTIF('Grade K-8'!B1:CR570,"1,51")=0,"",COUNTIF('Grade K-8'!B1:CR570,"1,51"))
=IF(COUNTIF('Grade K-8'!B1:CR570,"1,51")=0,"",COUNTIF('Grade K-8'!B1:CR570,"1,51"))

I need the red numbers to be ascending
is there an easy way to do this without having to go into each formula and manually change?

[Jonmo1]

Try

=IF(COUNTIF('Grade K-8'!B$1:C$R570,ROWS(A$1:A1)&",51")=0,"",COUNTIF('Grade K-8'!B$1:CR$570,ROWS(A$1:A1)&",51"))


However, I'd recommend putting the strings in their own cells, example...
In B1 put
=ROWS(A$1:A1)&",51"
and fill down as far as needed

Then use
=IF(COUNTIF('Grade K-8'!B$1:C$R570,B1)=0,"",COUNTIF('Grade K-8'!B$1:CR$570,B1))
and fill down.

[Losguapos1]

Thank for the input but that is way over my head. I have over 600 formulas that need to be updated. I wish i knew more and could take car of it in a more efficient manner.

What do mean by put the strings in thier own cell?

[Jonmo1]

Take the basic countif (without the testing if it's 0)

=COUNTIF('Grade K-8'!B1:CR570,"1,51")

The string "1,51" can be placed in a cell so your countif formula refers to that cell instead.
So say B1 = 1,51
=COUNTIF('Grade K-8'!B1:CR570,B1)


Now to make the 1 incirment to 2 to 3 as it's dragged down, that's where the ROWS() function comes in.
=ROWS(A$1:A1) = 1
As it's dragged down, it becomes
=ROWS(A$1:A2) = 2
=ROWS(A$1:A3) = 3
etc..

Then concatenate (join 2 strings together) with the rest of the string ",51"
=ROWS(A$1:A1)&",51"


Hope that helps.

[Losguapos1]

I have added the workbook I am working on, in the teacher summary page is where I need all of those formaulsa to return a value of an empty cell if the value is "0"

[mouthpear]

Thank you Very Much

[mouthpear]

Or a trick I recently learned...
I'm not familiar with MAC, but if it has the IFERROR function like XL2007+ on PC,

=IFERROR(1/(1/COUNTIF(...)),"")

Thank you Very Much