Remove "0" from data sheet

[Nitesh Kumar]

Hi All,

Kindly remove "0" from columnBin sheet 1, it would work on existing formula.

 

[Chihiro]

Your formula in existing set up can be shortened to...
=COUNTIFS(Sheet2!$D$2:$D$51,"hold*",Sheet2!$A$2:$A$51,A2)

To make it dynamic, replace range with dynamic named range.

To remove 0 there's several ways.

1. To remove 0 from display (but retain 0 as value in cell)
Go to Options->Advanced and scroll down to "Display options for this worksheet" and choose "Sheet1". Uncheck "Show a zero in cells that have zero value"

2. Similar to above but only applies to range and not entire sheet
Highlight range ->Format Cells->Custom
Format formula = 0;-0;;@

3. Use If function to return blank.
=IF(COUNTIFS(Sheet2!$D$2:$D$51,"hold*",Sheet2!$A$2:$A$51,Sheet1!A2)=0,"",COUNTIFS(Sheet2!$D$2:$D$51,"hold*",Sheet2!$A$2:$A$51,Sheet1!A2))

See attached sample.

 

[bosco_yip]

Another option to remove 0.

In Sheet1 C2, formula copy down :

=IFERROR(1/(1/COUNTIFS(Sheet2!D:D,"hold*",Sheet2!A:A,A2)),"")

Or,

In Sheet1 D2, enter formula:

=COUNTIFS(Sheet2!D:D,"hold*",Sheet2!A:A,A2)

and, D2 -> Format Cells -> Custom, in the Type box enter :

[=]g

copy down

Regards
Bosco

 

[Chihiro]

@bosco_yip

Cool use of #DIV/0! error. Now to go and update some of my sheets