 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2007sumproduct mod row
Hello folks, I'm hoping someone can help me since I have hit a snag in trying to modify a formula. This formula is working now (in J3) to sum starting with the value in row 11 and then adding every 7th row: =SUMPRODUCT((J11:J221)*(MOD(ROW(11:221),7)=4)) I want to adapt this (in O4) to sum starting with the value in row 17 and then add every 13th row. I've tried =SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=3)) but I get #VALUE. I'm guessing the problem is with the =3 at the end of the formula but I can't figure out what it should be. Any ideas? Thanks, Denise Hi Denise If you want to start on row 17 and use values for every 13th row, then =SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4))  SignatureRegards Roger Govier > Hello folks, > [quoted text clipped - 17 lines] > Thanks, > Denise Surely your formula should be: =SUMPRODUCT((O17:O407)*(MOD(ROW(17:407),13)=4)) It then works for me. Do you have #VALUE! in your data anywhere? SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hello folks, > [quoted text clipped - 15 lines] > Thanks, > Denise It works fine for me. Do you have any #VALUE! errors in O17:O407?? However, if you want to start with the value in row 17, use (MOD(ROW(17:407),13)=4) instead. > Hello folks, > [quoted text clipped - 12 lines] > Thanks, > Denise There aren't any visible #VALUE! errors in the range however when I evaluate the formula, I see #VALUE! in most of the cells. The way the spreadsheet is set up every other line is text: row 16 Concession row 17 1 row 18 Affidavit row 19 0 row 20 AWAS row 21 0 etc., with the items repeating every 13th row (rows 15-27 are for Day 1, rows 28-40 for Day 2...) Could the text be causing the #VALUE! error? Thanks, Denise > It works fine for me. Do you have any #VALUE! errors in O17:O407?? > [quoted text clipped - 20 lines] > > Thanks, > > Denise Try working out which cell one of the #VALUE! returns is being returned from - say it was A65 - then try in an empty cell the formula: =ISNUMBER(A56) If you do not get TRUE then the entry is text even if it looks like a number SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > There aren't any visible #VALUE! errors in the range however when I > evaluate [quoted text clipped - 45 lines] >> > Thanks, >> > Denise Hi Sandy, Thanks for your suggestion. I do get False for some of the cells that should be numbers. I set the format to number and still get False. These number cells are actually formulas to pull the number results from a different sheet: ='1'!T22 Denise > Try working out which cell one of the #VALUE! returns is being returned > from - say it was A65 - then try in an empty cell the formula: [quoted text clipped - 52 lines] > >> > Thanks, > >> > Denise Denise, Re-formatting will not change what is actually being held in the cell. Having said that I am about to tell you to do just that! Try entering a new formula: =--('1'!T22) That will change the cell contents into a number so try =ISNUMBER(A56) should be TRUE and =ISTEXT(A65) should return FALSE When I tested it in XL97 it automatically re-formatted the cell to Text so I re-formatted it back to General just to be consistent' Then evaluate the formula againa and see it you are still getting the #VALUE! SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hi Sandy, > [quoted text clipped - 68 lines] >> >> > Thanks, >> >> > Denise Ok, things are getting curiouser and curiouser... That fixed the problem except when the source cell ('1'!T22) is blank then it reverts to #VALUE! If there is a number in the cell, all now works fine. I think I am going to re-work the layout so I have consecutive rows of number without all those text rows. Thanks much for all your help! Denise > Denise, > [quoted text clipped - 89 lines] > >> >> > Thanks, > >> >> > Denise Denise, I assume that when you say that the cell is blank you mean it is actually returning an empty string, (ie ""), which is text. I assume therefore that '1'!T22 is a cell formatted as general with a formula which is returning a text representation of a number or an empty string. I would therefore look at that cell. SignatureGood luck, post back if you need further help Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Ok, things are getting curiouser and curiouser... > [quoted text clipped - 109 lines] >> >> >> > Thanks, >> >> >> > Denise |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.