How do I get my formula to show blank when the data is actually blank, and zero when it's actually zero.

[AlenK37]

Hello. I'm relatively new to excel and been stuck on a formula now for a that I can't seem to figure out. For most of my data, I am using SUMIF's to get the information I need, which works good for about 99% of the data I need. However, it's leaving the zeros blank, but I need it actually show zeros as well when the result is zero. What is the best approach for this? Perhaps other formulas would be better? I'm currently extracting all the data into a table and referencing the data in my formulas.

Here is my current formula I am using: =IF(SUMIFS($D:$D,$A:$A,$AK127)="","",SUMIFS($D:$D,$A:$A,$AK127))

I've tried many variations of this code, but I still keep getting zero's for empty results.

Side note, the data actually shows a "−" symbol when there is no data, not a blank

Any help or guidance would be much appreciated

 

[AlenK37]

Just to confirm, I want it to remain blank if there is no data, and I want it show zero if the result is actually zero. Both can be true, just not at the same time

 

[Eric W]

Check out this article. It shows several ways.

Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most...

www.mrexcel.com

 

[AlenK37]

Thanks, but I need some guidance on where to start? For context, I've spent quite a lot of time trying to go through various Google searches and AI before resorting to this forum. Despite everything I tried searching for, I have not gotten the result I need. Maybe I'm not looking for the right thing, which is why I'm here. So any guidance beyond a link reference is much appreciated

 

[Alex Blakenburg]

the data actually shows a "−" symbol when there is no data, not a blank

Typically that does not mean no data, it means that a custom number format has been applied to the cell and the 0 has been formatted to show "-".
Click in one of the cells that contains a "-" and show us what is showing in the formula bar.

 

[Scott Huish]

SUMIFs wont return "", it returns a number:
Try this:

=IF(SUMIFS($D:$D,$A:$A,$AK127),SUMIFS($D:$D,$A:$A,$AK127),"")

This will show the result if SUMIFS returns a non-zero number (TRUE), otherwise shows ""

 

[AlenK37]

Typically that does not mean no data, it means that a custom number format has been applied to the cell and the 0 has been formatted to show "-".
Click in one of the cells that contains a "-" and show us what is showing in the formula bar.

The screenshot I

Typically that does not mean no data, it means that a custom number format has been applied to the cell and the 0 has been formatted to show "-".
Click in one of the cells that contains a "-" and show us what is showing in the formula bar.

Shows : "−"

 

[AlenK37]

SUMIFs wont return "", it returns a number:
Try this:

=IF(SUMIFS($D:$D,$A:$A,$AK127),SUMIFS($D:$D,$A:$A,$AK127),"")

This will show the result if SUMIFS returns a non-zero number (TRUE), otherwise shows ""

 

[Alex Blakenburg]

Your screenshot didn't come through.
Are you saying in the formula bar you see a "-" ? How is the data getting there ?
I would have expected it to show either a formula or a 0.

 

[AlenK37]

This is still giving me blank when it should be zero. Maybe SUMIF's wont work for this specific function?