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

Options

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!

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 12/19/23
    Options

    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
    SteyJ ✭✭✭✭✭✭
    edited 12/19/23
    Options

    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, "")
    
    Sincerely,

    Jacob Stey

  • Luke W.
    Luke W. ✭✭✭✭✭
    Options

    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.


    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).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!