Ignore blank cells

[Zagra147]

I have a row of cells, say, A1 to G1.
I wish to sum the values in cells, say, A1, C1, F1 and G1 only. The chosen cells can have values 0, >0 or Blank. How do I sum the values and ignore the blank cell(s) without getting #VALUE!?
Filling the blank cells with "0" is not an option.

(Any blank cells may have a value added at a later date).

Edit: I could use multiple 'IF' statements, but this is cumbersome. There must be a simpler way.

Thanks in advance

Alan

[popipipo]

=SUM(A1,C1,F1,G1)
Does this work?

[Zagra147]

No. The blank cell(s) gives "#VALUE!"

[Winon]

Hello Zagra147,

It would depend on what you have in the Cells to be summed, like Formulae, Text, etc.

Could you show us what you have in the respective cells in question?

Regards

[AlKey]

Try this one

=AGGREGATE(9,6,A1,C1,F1,G1)

[Zagra147]

Solved it using AGGREGATE(9,6,A1,C1,F1.G1).

9 = SUM and 6 = Ignore error values.

I knew there must be a simpler way than us loadsa 'IF' statements.

Seems to be a very useful function - haven't used it before.

Regards to all who have thought about this.

Alan

[Zagra147]

Alkey
Thanks.
I think our posts crossed!

Alan

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[icestationzbra]

for versions lower than 2010, this could also be an option (a trick i learnt from Domenic):

Please Login or Register  to view this content.

a little more dynamic array-formula:

Please Login or Register  to view this content.

these would work in case errors existed in cells.

[Zagra147]

Winon

Just simple numbers - the AGGREGATE function works fine.

Alan

[Winon]

Hello Zagra147,

Thank you for the feedback.

I am on Excel 2007, which doesn't have the AGGREGATE feature, so I could not consider it as a solution. Maybe it is time for me to upgrade.

Regards.

[Zagra147]

icestationzbra

SUM(SUMIF(INDIRECT({"a1","c1","f1","g1"}),">0"))

That too works, but only for a single row - it cannot be copied down a column unless you do a lot of manual editing.

Regards

Alan

[Winon]

@ icestationzbra,

Very nice alternative, which I would prefer using to handle Excel versions earlier than 2010.

Regards

[Zagra147]

I jumped from 2000 to 2010 so I haven't figured out all the extra functions yet...

[Winon]

This would handle just about all versions of Excel,

=SUM(SUM(A1),SUM(C1),SUM(F1),SUM(G1))

[Zagra147]

Winon

=SUM(SUM(A1),SUM(C1),SUM(F1),SUM(G1))

That too works a treat

Regards

Alan

[Zagra147]

Very informative posts.

Thanks one and all.

Alan

[Winon]

Zagra147,

Thank you for the feedback.

Personally, I prefer to try and formulate in such a way that things are not limited to later versions of the earliest workable Excel options.

Kind Regards.

[Winon]

@ Zagra147,

Thank you for adding to my Reputation.

Best Regards.