Hi,
I have a problem with an array formula where I want to sum the values in every 7th row. The problem I have is I am receiving #VALUE! in my returing cell of the formula when there is text in the array. Is there a way I can avoid including the text in the array or maybe error checking using ISERROR and ISNUMBER.
The array formula I am entering is:
thanks
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi and welcome to the forum!
You could use (array-entered):
=SUM(IF(ISNUMBER(C8:C192),C8:C192*(MOD(ROW(C8:C192),7)=0)))
On a side note, I take it you're aware that, should your ranges change, a construction such as MOD(ROW(Range),7, although it will continue to give you the desired "every seventh row", may not necessarily begin on your desired starting cell?
If this is something which may become a factor, and it's always the 7th, 14th, 21st, etc. cells which are to be considered, whatever the range, you will need to use e.g.:
=SUM(IF(ISNUMBER(Range),Range*(MOD(ROW(Range)-MIN(ROW(Range))+1,7)=0)))
Regards
Last edited by XOR LX; 02-04-2014 at 06:22 PM.
Thanks both rcm & XOR LX for your replies. I tried XOR LX first option and it worked perfectly. I've also noted the second option for future reference but the first will do for now. thanks a million.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks