SUM help please
"Ron Rosenfeld" wrote:
=SUMPRODUCT((MOD(ROW($A$1:$A$1000)-FirstRow,3)=0)*$A$1:$A$1000)
Actually, the above formula will only work if all of the entries
are numeric. If some of the entries are text, then the above
formula will give a VALUE error. To avoid that, you can use the
following formula:
This formula must be **array-entered**:
=SUM(IF(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0, $A$1:$A$1000))
No need to abandon SUMPRODUCT and use the more complicated and error-prone
array-entered formula. Simply write:
=SUMPRODUCT(--(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0),$A$1:$A$1000)
The double negative converts TRUE and FALSE to 1 and 0, which SUMPRODUCT
requires, as the multiplication did before.
|