Counting from a reference sheet - Don't show Zero Count
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
-
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)
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!