adding problem with formulas

[daveallston]

This may be just me spending too much time today in Excel, but I'm
having a problem with what I think is a simple problem.

I have multiple tabs in a spreadsheet, with tabs reading off of other
tabs.

I'm at the main tab, and it needs to total up 3 cells (lets call them
N20, N22 and N24), into cell E37 (E37 being the total of N20 N22 and
N24 added up together). The 3 cells it needs to total up (N20+N22+N24)
are formula cells linking to other tabs. And those 3 cells are
dependent on if D20, D22 and D24 have a value, then N20, N22 and N24
will have values. For example, if say, D22 doesn't have a value, then
it needs to only add N20 and N24 (well it could still add N22, but N22
has a zero value).

But I get the damn "#VALUE!" notice when I try to add the 3 cells up,
when one of the cells does not have a value. Like when D24 has no
value, therefore N24 has no value. So adding N20+N22+N24=#VALUE!. But
if all 3 have a value, then N20+N22+N24=an actual number.

Does this make sense? What formula should I use to calculate these,
whether there is 3 values in N20, N22 and N24 or not.

As an extra question, what can I add in to make sure that a 0 does not
appear in E37 when all 3 of N20, N22 and N24 have no values. What can I
add in to the formula to make sure E37 remains blank, rather than
showing a "0"?

Thanks very much in advance for your help.
Dave

 

[Biff]

Try this:

=IF(COUNT(N20,N22,N24),SUM(N20,N22,N24),"")

Biff

 

[Ron Rosenfeld]

This may be just me spending too much time today in Excel, but I'm
having a problem with what I think is a simple problem.

I have multiple tabs in a spreadsheet, with tabs reading off of other
tabs.

I'm at the main tab, and it needs to total up 3 cells (lets call them
N20, N22 and N24), into cell E37 (E37 being the total of N20 N22 and
N24 added up together). The 3 cells it needs to total up (N20+N22+N24)
are formula cells linking to other tabs. And those 3 cells are
dependent on if D20, D22 and D24 have a value, then N20, N22 and N24
will have values. For example, if say, D22 doesn't have a value, then
it needs to only add N20 and N24 (well it could still add N22, but N22
has a zero value).

But I get the damn "#VALUE!" notice when I try to add the 3 cells up,
when one of the cells does not have a value. Like when D24 has no
value, therefore N24 has no value. So adding N20+N22+N24=#VALUE!. But
if all 3 have a value, then N20+N22+N24=an actual number.

Does this make sense? What formula should I use to calculate these,
whether there is 3 values in N20, N22 and N24 or not.

As an extra question, what can I add in to make sure that a 0 does not
appear in E37 when all 3 of N20, N22 and N24 have no values. What can I
add in to the formula to make sure E37 remains blank, rather than
showing a "0"?

Thanks very much in advance for your help.
Dave

You are probably getting the VALUE error because your formulas are resulting in
one of the cells be added having a null string (""). That is different from
having "no value" or being empty.

That being the case, SUM should work: =SUM(N20, N22, N24)


--ron

 

[Ron Rosenfeld]

This may be just me spending too much time today in Excel, but I'm
having a problem with what I think is a simple problem.

I have multiple tabs in a spreadsheet, with tabs reading off of other
tabs.

I'm at the main tab, and it needs to total up 3 cells (lets call them
N20, N22 and N24), into cell E37 (E37 being the total of N20 N22 and
N24 added up together). The 3 cells it needs to total up (N20+N22+N24)
are formula cells linking to other tabs. And those 3 cells are
dependent on if D20, D22 and D24 have a value, then N20, N22 and N24
will have values. For example, if say, D22 doesn't have a value, then
it needs to only add N20 and N24 (well it could still add N22, but N22
has a zero value).

But I get the damn "#VALUE!" notice when I try to add the 3 cells up,
when one of the cells does not have a value. Like when D24 has no
value, therefore N24 has no value. So adding N20+N22+N24=#VALUE!. But
if all 3 have a value, then N20+N22+N24=an actual number.

Does this make sense? What formula should I use to calculate these,
whether there is 3 values in N20, N22 and N24 or not.

As an extra question, what can I add in to make sure that a 0 does not
appear in E37 when all 3 of N20, N22 and N24 have no values. What can I
add in to the formula to make sure E37 remains blank, rather than
showing a "0"?

Thanks very much in advance for your help.
Dave

Never mind my response. I missed part of your question and I see that Biff has
posted an appropriate answer.
--ron

 

[daveallston]

Hi
Thanks a lot for the reply. Much appreciated, and it worked perfectly.
I have one similar example, but with a slight twist. In another area, I
need to do a similar equation with similar data (use the same N20, N22
and N24 from my above example), but instead of summing all three cells
in cell E37, I need to do an equation like N20+N22-N24 or possibly
N20+N24-N22. But again, one of those cells (either N20, N22 or N24) may
have no value. How can I type this formula in E37, since when i throw
in the "minus" part of the equation, it gives me the "#VALUE!" thing
again.

Thanks again! Your help is much much appreciated.
Dave

 

[Biff]

You'd have to test and make sure the value you're subtracting is indeed a
number:

One of these:

=SUM(N20,N22,-IF(ISNUMBER(N24),N24,0))
=SUM(N20,N22)-IF(ISNUMBER(N24),N24,0)
=SUM(N20,N22,-IF(N24="",0,N24))
=SUM(N20,N22)-IF(N24="",0,N24)

Biff