Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some column
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 -- Brent |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some column
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! In article , Brent wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some column
do the columns have cells returing a #DIV/0 error?
-- Regards, Tom Ogilvy "Brent" wrote: 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 -- Brent |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some co
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. -- Brent "Tom Ogilvy" wrote: do the columns have cells returing a #DIV/0 error? -- Regards, Tom Ogilvy "Brent" wrote: 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 -- Brent |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some co
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! In article , Brent wrote: 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. -- Brent "Tom Ogilvy" wrote: do the columns have cells returing a #DIV/0 error? -- Regards, Tom Ogilvy "Brent" wrote: 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 -- Brent |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum every 4th cell returns #DIV/0! error in some co
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 article , Domenic wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
copy and pasting a find all list into another column | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |