Totalling formulas

[Guest]

Hello from Steved

I have formulas in cell C3,C6,C9,C12,C15,C18,C21,C24 and C27

If I do this =C3+C6+C9+C12+C15+C18+C21+C24+C27
I get a #VALUE! in the cell

Please What is require for me to get an answer.

Thankyou.

 

[Domenic]

Try...

=SUM(C3,C6,C9,C12,C15,C18,C21,C24,C27)

Hope this helps!

 

[Guest]

Hello from Steved

Below is my attempt but is returning a #NAME! Value.

=SUM(IF(MOD(COL($C$3:$C$29)-1,$C$3)=0,$C$3:$C$29,0))
Thankyou.

 

[Morrigan]

If those cells do not return a number, you can not add them up or excel
will give you an error message like #VALUE!

If you must sum them, do this:

=SUMIF(A:A,">0")+SUMIF(A:A,"<=0")

 

[Guest]

Thanks Domenic

Try...

=SUM(C3,C6,C9,C12,C15,C18,C21,C24,C27)

Hope this helps!

 

[Sandy Mann]

There is no COL function in my version of XL so i assume that is why you get
the #NAME! error

Try:

=SUM((IF(MOD(ROW(C1:C29),3)=0,C1:C29)))
array entered or

=SUMPRODUCT(((MOD(ROW(C1:C29),3)=0)*(C1:C29)))
normally entered

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Dana DeLouis]

My "guess" is that if "Sum" worked, but =C3+C6+... did not work, then one of
your cells has text, and is not a number.
As technique, pull up the "Formula Auditing" toolbar, select your cell with
the value error, and click on the "Trace Error" button. It should point you
to the text cell that is causing the error.

HTH :>)

 

[Guest]

Hello Sandy from Steved

Thankyou.

There is no COL function in my version of XL so i assume that is why you get
the #NAME! error

Try:

=SUM((IF(MOD(ROW(C1:C29),3)=0,C1:C29)))
array entered or

=SUMPRODUCT(((MOD(ROW(C1:C29),3)=0)*(C1:C29)))
normally entered

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk