IFERROR function

**Batman11692003** · 10-16-2011, 01:16 PM

When I use the function below it hides all the blank values (when there's nothing in the field entered) but when the value is equal to 0 in the field, it does not show the value as 0. How can show the value of 0 and not show the blank value to show up as 0.

=IFERROR(1/(1/SUMIFS(Cost!$C$2:$C$7,Cost!$A$2:$A$7,B2,Cost!$B$2:$B$7,C2)),"")

**daddylonglegs** · 10-16-2011, 01:35 PM

Which fields should be blank for the formula to show a blank, all of Cost!$C$2:$C$7 or just on the rows where the two conditions are satisfied?

**Batman11692003** · 10-16-2011, 01:59 PM

If there is no numbers in the field Cost!$C$2:$C$7 it should be blank, but if there are numbers such as 0 in the same field it should show the value 0.

**daddylonglegs** · 10-16-2011, 03:27 PM

Perhaps try this version

=IF(COUNT(Cost!$C$2:$C$7),SUMIFS(Cost!$C$2:$C$7,Cost!$A$2:$A$7,B2,Cost!$B$2:$B$7,C2),"")

**Batman11692003** · 10-16-2011, 03:42 PM

It is properly showing a 0 when a 0 appear in the cell. I used your function for the main tab in D2.

But if the cell field is blank or contains text such as NA, it should show the field as blank instead of a 0.

I also attached an excel sheet with the value I am looking for.