Show blanks instead of 0

[batman11692003]

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)

 

[T. Valko]

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

 

[batman11692003]

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

 

[shg]

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

 

[batman11692003]

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

 

[T. Valko]

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?

 

[batman11692003]

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.

 

[T. Valko]

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.

 

[batman11692003]

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
6 PC Touchpad

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

 

[T. Valko]

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
6 PC Touchpad

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

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