Formula to sum every 4th cell returns #DIV/0! error in some column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brent - 16 Mar 2006 18:00 GMT

I use this formula to sum the values in every fourth cell in colums
=SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of
the colums, but returns the error #DIV/0! in the other colums. I do not get
why I get the error only some of the time.

What causes this and how do I correct it?

Thanks

Signature

Brent

Reply to this Message

Domenic - 16 Mar 2006 19:28 GMT

Do you mean a #VALUE! error? If so, then it's likely that your range of
cells contains one or more text values. Maybe your range contains
formula blanks. Try the following syntax instead...

=SUMPRODUCT(--(MOD(ROW(E7:E190)-ROW(E7),4)=0),E7:E190)

...which ignores text values, including formula blanks.

Hope this helps!

> I use this formula to sum the values in every fourth cell in colums
> =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

Tom Ogilvy - 16 Mar 2006 19:52 GMT

do the columns have cells returing a #DIV/0 error?

Signature

Regards,
Tom Ogilvy

> I use this formula to sum the values in every fourth cell in colums
> =SUMPRODUCT((MOD(ROW(E7:E190),4)=3)*(E7:E190)). This works in about half of
[quoted text clipped - 4 lines]
>
> Thanks

Reply to this Message

Brent - 16 Mar 2006 19:55 GMT

Hey Tom,

Yes, in some of the colums the cell with this formula displays #DIV/0!, in
other columns I get the desired sums. All the cells with this formula are in
the same row (row 198) and refer to data in rows 7 - 195..

Thanks for your interest and help.

Signature

Brent

> do the columns have cells returing a #DIV/0 error?
>
[quoted text clipped - 6 lines]
> >
> > Thanks

Reply to this Message

Domenic - 16 Mar 2006 20:12 GMT

In that case, try the following formula...

=SUM(IF(MOD(ROW(E7:E190)-ROW(E7),4)=0,IF(ISNUMBER(E7:E190),E7:E190)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

> Hey Tom,
>
[quoted text clipped - 16 lines]
> > >
> > > Thanks

Reply to this Message

Domenic - 16 Mar 2006 20:20 GMT

An alternative is to have your formulas return 0 or blank instead of
#DIV/0!. If you have your formulas return a blank, you can use the
first formula I offered...

> In that case, try the following formula...
>
[quoted text clipped - 3 lines]
>
> Hope this helps!

Reply to this Message