How can I add group of cells where the formula results in those cells aren't " "?

Hi Joe,

" " inserts a space which looks blank but isn't.

"" inserted from a formula gives a NULL character which also looks blank (but is a string of zero length). Maybe that will work.

If not, another idea is to insert zero and then conditionally highlight cells with zero to white text (that also appears blank 😉).

Regards,

Ian.

Hi again Joe,

The SUM function may be easier than using + for every cell.

I tested "" in a column with some values. Every "blank" cell contains the formula ="" (a much shorter version of the formula you will use!)

Bottom left left of the window shows the formula.

To get the sum, B8 contains this formula:

=SUM(B)

This works because Row 1 is a Header Row and Row 8 is a Footer Row which excludes them from the Body Cells in B (B2 to B7). Otherwise B8 would refer to itself and show a self reference error.

Regards,

Ian

Hi Joe,

re-working of other calculations & sheets that acknowledge " " as a stopping point in IF statements.

Aahaa! The plot thickens!

Please reply with a screen shot of a small part of your screen (large shots are often difficult to read).

To take a screen shot,

1. hold down the shift and command keys, then type 4. The cursor will change to crosshairs. Release the shift and command keys.

2. Drag over that part of your screen then release the mouse/trackpad. A screen shot will appear on your desktop.

3. In a reply to a message, click on the camera icon in the Toolbar above your reply and

4. Choose File > Choose > Insert Image.

If this does not work, Close the Insert Box (red cross on the top right) and go back to Step 3. Camera icon sometimes needs a wake-up call, but works the second time.

Remove any personal details before taking the screen shot.

Regards,

Ian.

Hi Joe,

"The operator +" chokes on text (including the empty string).

The Function SUM, given a full column reference, a range of cells, or a list of cells, ignores cells containing text.

Formulas shown in column A are entered in the adjacent cell in column B. Either of the entries in C6 ( = "" ) and C9 ( text ) will triggere the error shown.

Regards,

Barry

Hi Joe,

You can keep " " in your fomulas if you use SUM instead of +

Columns B and C have " " where you want to show a 'blank' cell.

=SUM(B) in B8 works.

In C8 the formula is

=C2+C3+C4+C5+C6+C7

and shows the same error message that you got.

Regards,

Ian.

Hi Joe,

See the formula in A4 of my screen shot. You'll need to use something like this as you are adding non-contiguous cells. In the Actual column, enter =SUM() then place the insertion point between the parentheses and alternate these actions:

Click one of the coloured cells,

type a comma

Repeat

No comma following the last cell clicked.

Regards,

Barry