# Show blanks instead of 0

#### batman11692003

##### New Member
I have this code I am using, but if any of the values are blank in Cost!\$C\$2:\$C\$7 the function shows a 0 value. How can I change this to show a blank instead of 0.

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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I have this code I am using, but if any of the values are blank in Cost!\$C\$2:\$C\$7 the function shows a 0 value. How can I change this to show a blank instead of 0.

=SUMIFS(Cost!\$C\$2:\$C\$7,Cost!\$A\$2:\$A\$7,B2,Cost!\$B\$2:\$B\$7,C2)
One way...

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

That worked perfect, but now my conditional formatting that calls the data from that cell shows up yellow when it sees "". How would i change it to show blanks

Why not just change the cell formatting to suppress display of 0, e.g., 0;-0;;

Would the value 0 still show up if the cell value was 0 in the field and be blank if the cell value was blank in the field.

One way...

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

That worked perfect, but now my conditional formatting that calls the data from that cell shows up yellow when it sees "". How would i change it to show blanks

Would the value 0 still show up if the cell value was 0 in the field and be blank if the cell value was blank in the field.
What's your conditional formatting rule?

I fixed my conditional formatting rule by changing the order sequences. But if the value is 0, it does not show 0 as a value with the iferror function

Also if the value is blank or contains NA in the field, i would like it to show a blank cell instead of showing a zero.

I fixed my conditional formatting rule by changing the order sequences. But if the value is 0, it does not show 0 as a value with the iferror function

Also if the value is blank or contains NA in the field, i would like it to show a blank cell instead of showing a zero.
It's not clear what you're asking.

For Row 2 on the main tab, it should show the value of 0 from the Cost tab

Main Tab
Type Error Cost
1 PC Harddrive 50
2 MAC Harddrive
3 PC Monitor
4 MAC Monitor 120
5 PC Keyboard 30

Cost Tab
Type Error Cost
PC Harddrive 50
MAC Harddrive 0
PC Monitor
MAC Monitor 120
PC Keyboard 30

For Row 2 on the main tab, it should show the value of 0 from the Cost tab

Main Tab
Type Error Cost
1 PC Harddrive 50
2 MAC Harddrive
3 PC Monitor
4 MAC Monitor 120
5 PC Keyboard 30

Cost Tab
Type Error Cost
PC Harddrive 50
MAC Harddrive 0
PC Monitor
MAC Monitor 120
PC Keyboard 30
Ok, I think I understand what you're saying:

0 can be a valid result

Assuming the cost will never be a negative number...

=IF(COUNTIFS(Cost!A\$2:A\$7,A2,Cost!B\$2:B\$7,B2,Cost!C\$2:C\$7,">=0"),SUMIFS(Cost!C\$2:C\$7,Cost!A\$2:A\$7,A2,Cost!B\$2:B\$7,B2),"")

Replies
3
Views
144
Replies
1
Views
204
Replies
6
Views
192
Replies
7
Views
435
Replies
6
Views
174

1,206,759
Messages
6,074,777
Members
446,087
Latest member
PinkFloyd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back