Cell to be blank if reference cell is blank or show value if value is there even when value is zero

[HopefulGJL]

Hello,

I have a multiple tab spreadsheet.

The following formula works perfectly with one exception:
=Sheet1!C11+Sheet2!C11+Sheet3!C11

The issue (exception) is that it returns a zero even when C11 on Sheet 1 is blank. If C11 on Sheet 1 is blank, then I need the above formula (or some variation thereof) to be blank.

If C11's value is zero (0), then I need the above formula (or some variation thereof) to reflect 0 (zero).

If C11's value is say 5, then I need the above formula (or some variation thereof) to reflect 5.

I have been searching for the answer online and have tried multiple combinations and still have not found the answer. I really appreciate any assistance you can offer in advance.

 

[bbott]

=IF(Sheet1!C11="","",Sheet1!C11+Sheet2!C11+Sheet3!C11)

 

[HopefulGJL]

Thank you. That has returned the value of false regardless of what I have in Sheet1 C11. Are there any additional suggestions?

 

[bbott]

When you get "FALSE", what values are in the three cells being summed? Does it return "TRUE" when you delete the value from cell C11 on Sheet 1?

 

[Joe4]

Thank you. That has returned the value of false regardless of what I have in Sheet1 C11. Are there any additional suggestions?

Sounds like you mis-typed the formula given to you. Use Copy & Paste to avoid typos.

I am guessing you used this:

[COLOR=#333333]=IF(Sheet1!C11="",Sheet1!C11+Sheet2!C11+Sheet3!C11)[/COLOR]

instead of this:

[COLOR=#333333]=IF(Sheet1!C11="","",Sheet1!C11+Sheet2!C11+Sheet3!C11)[/COLOR]

 

[HopefulGJL]

Thank you for pointing that out as that did help me realize I actually used "," the first time instead of "","".

This is working perfectly for Sheet1.

I now need to take this and apply it to every sheet. In the real sheet, there are 23 sheets. I need all 23 sheets to add up on sheet 24. But, no matter what sheet's C11 has in it, I need it to be a blank cell on sheet 24 if all other 23 sheets are blank; I need it to reflect 0 (zero) if all 23 sheets are 0 when added together; and, I need it to reflect the total number of all sheets if there are numbers to add up across the 23 sheets.

Now that I understand how to create this with just sheet 1, I could do this with every sheet, but the formula would be very cumbersomely long. Is there a way to consolidate/shorten it somehow?

 

[Joe4]

This formula should do what you want:

=IF(COUNT(Sheet1:Sheet23!C11)>0,SUM(Sheet1:Sheet23!C11),"")

 

[HopefulGJL]

Thank you; I have attempted that general format.

In the real sheet, the tabs have specific names.

I therefore took the formula above and applied this format to it:
=(IF(COUNT(Apple_1!C11+Beach2!C11+Tire_ABC!C11)>0,SUM(Apple_1!C11+Beach2!C11+Tire_ABC!C11),"")

Since the tabs need to be named (and, no, those aren't the real names), I think the Sheet1:Sheet23 range format would not work, correct?

Yet, when I apply the provided format to what I reflect in this post, I'm still getting a result of 0 (zero) even though all 23 sheets are blank (and I need it to be blank on this sheet 24 since all other 23 sheets are blank in cell C11).

Does anything need to be changed because I can't use the Sheet1:Sheet23 range format?

 

[HopefulGJL]

Through hours of more research plus trial and error, it apperas I've discovered something that works.

While not elegant, and quite long, here's what I've got:

=(IF(COUNTA(Apple_1!C11,Beach2!C11,Tire_ABC!C11)<1,"",SUM(Apple_1!C11+Beach2!C11+Tire_ABC!C11))

If anyone knows of a more elegant solution or why it would be inadvisable to utilize this, I'd welcome the suggestions (especially since I have 23 named sheets to do this with)!

 

[Joe4]

Since the tabs need to be named (and, no, those aren't the real names), I think the Sheet1:Sheet23 range format would not work, correct?

Sure it would. Just replace "Sheet1" with the name of your first sheet, and "Sheet23" with the name of the last sheet. The names really don't matter, just the order they appear.

=(IF(COUNTA(Apple_1!C11,Beach2!C11,Tire_ABC!C11)<1,"",SUM(Apple_1!C11+Beach2!C11+Tire_ABC!C11))

You formula is very similar to the one I posted. You could shorten it up a bit like this:

[COLOR=#333333]=(IF(COUNTA(Apple_1:Tire_ABC!C11)<1,"",SUM([/COLOR][COLOR=#333333]Apple_1:Tire_ABC!C11[/COLOR][COLOR=#333333]))[/COLOR]

Note, using SUM and addition in the same formula is redundant and unnecessary (kind of like a double-positive). You only need one or the other, since they both do the same thing.