Counting from a reference sheet - Don't show Zero Count

Luke W. · 12/19/23

Hello All,

Is there a way to NOT display "0" in my calculation sheet when counting items from another sheet?

Here is my current formula:

=COUNTIFS({DWR}, "Green", {Week}, [Wk1]$1, {Shift}, $Shift@row, {Crew}, $Crew@row) / 48

When this is "0", I would like the cell to be blank so it is not calculated in my averages.

Possible?

Thank you for your help!

Lucas Rayala · 12/19/23

Hi @Luke W. the super easy cheater way is to just do this:

=IF(<Original Formula> = 0, "", <Original Formula>)

It gets a little messy and sucks your cell count (you can only reference 2M cells on another page), so if you're resource constrained you may want to figure out a different path forward. However, it works!

The full messy version:

=IF(COUNTIFS({DWR}, "Green", {Week}, [Wk1]$1, {Shift}, $Shift@row, {Crew}, $Crew@row) / 48 = 0, "", COUNTIFS({DWR}, "Green", {Week}, [Wk1]$1, {Shift}, $Shift@row, {Crew}, $Crew@row) / 48)

SteyJ · 12/19/23

What kind of data produces a zero? Is it possible to change the rows referenced to a value that will cause an error for crew member? If so, you can use IFERROR to have nothing return like below.

=IFERROR(COUNTIFS({DWR}, "Green", {Week}, [Wk1]$1, {Shift}, $Shift@row, {Crew}, $Crew@row) / 48, "")

Luke W. · 12/19/23

https://community.smartsheet.com/discussion/comment/410343#Comment_410343

This formula is on a "Pivot Table" that has some calculations from another sheet. If nothing is entered on the other sheet, then it shows "0" for the calculation because there is nothing to count.

https://community.smartsheet.com/discussion/comment/410340#Comment_410340

This worked! Thank you.

I'll give it a go and see if it slows things down or not.

I still would like to learn the "proper" way of doing this (if there is one).

Counting from a reference sheet - Don't show Zero Count

Answers

Sincerely,

Help Article Resources

Categories