Need help about some basic stuff

[Jumbala]

(Kinda long explanation here to make people understand what I want t
know, just *read the summary if you don't want to read everything her
for topics I need help with*)

All right, so I haven't used Excel in years so there are some things
don't remember and I'd really appreciate it if someone could help m
with that.

I've got two simple questions (it's only one question split in two
guess):

Let's say I want cell A4 to display the sum of A1 to A3. To do that,
wrote =SUM(A1:A3).

It works, but I don't want cell A4 to display a result when there ar
no values in cells A1 to A3, right now, it's displaying "0".

I thought I could fix it with an if statement, so I wrot
=IF(SUM(A1:A3)=0; ""; SUM(A1:A3))

That works as well, but the problem is I'd need another IF for the sam
cell for what I want to do. I want it to be like:

- If cells A1:A3 have values in them, display the sum of those cells.
- If cells A1:A3 have nothing in them, and cell B4 also has nothing i
it, display nothing in A4.
- If cells A1:A3 have nothing in them, and cell B4 has a value in it
display "-" in A4.

*Summary:*
1. How do I make it so empty cells that have a formula display nothin
instead of displaying "0"?

2. How do I use multiple IF statements for the same cell if I hav
different things to display for different cases?

Thanks in advance for the help, I don't remember how to use Excel al
that much after all these years, unfortunately

 

[T. Valko]

I'm assuning B4 is supposed to be a numeric value.

Try this:

=IF(COUNT(A1:A3,B4)=0,"",IF(AND(COUNT(A1:A3)=0,COUNT(B4)),"-",IF(COUNT(A1:A3),SUM(A1:A3),"")))

You might have to change the commas to semicolons if your regional settings
use semicolons.

 

[Ron@Buy]

Two other ways of 'hiding' that 0
1st option - Tools > Options > View tab - untick "Zero Values"
Drawback the value of zero will not appear anywhere on your worksheet!
2nd option - This is cell specific - Format > Conditional Formatting -
select 'Cell Value is' > 'Equal to' > 0 > Format > Font and select color
white.
You can of course copy this formatting to as many cells as you like.