 |
 | 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 / December 2005count problem
I have in column AU cell 39 to cell 500 random sequences of 1-10 rows , with numbers in them, (as an result of formulas) I need to count the number of rows in each sequence that have numbers of 35 or less , but only up to the first number of 35 or higher. eg:- AU result row 39 32 3 40 14 41 19 42 107 43 11 44 21 45 #value! 46 40 47 45 48 16 49 15 50 #value! 51 29 2 52 11 53 146 54 14 55 17 56 11 57 #value! and so on down to 500 rows all help is appreciated thanks bill gras Replace "result" with 0 in AV38 and enter in AV39 & copy down: =IF(OR(ISERROR(AU39),SUMIF(AU39,">35")>35), "", IF(IF(ISERROR(AU40),1,AU40>35), COUNTIF(INDEX($AU$39:AU39,MATCH(9.99999999999999E+307,$AV$38:AV38)):AU39,"<=35"), "")) > I have in column AU cell 39 to cell 500 random sequences of 1-10 rows , > with numbers in them, (as an result of formulas) [quoted text clipped - 27 lines] > thanks > bill gras Hi Aladin, Thank you for your time and your reply There is one thing that your formula does not do is: count all numbers under 35 , stop at the first number larger than 35 and than go to the next sequence , and count all numbers under 35 stop at the first number larger than 35 and than go to the next sequence , and so on . Can that be done ? I apologise if I did not explained my self properly the first time . as per your formula :- -- AU > row 39 32 > 40 14 [quoted text clipped - 15 lines] > 56 11 3 > 57 #value! I hope you can still help me as I'm nearly finished my project Thanking You regards bill bill gras > Replace "result" with 0 in AV38 and enter in AV39 & copy down: > [quoted text clipped - 36 lines] > > thanks > > bill gras > Hi Aladin, > Thank you for your time and your reply [quoted text clipped - 17 lines] >> 45 #value! >> 46 40 [...] Are you trying to say that 3 should appear at the same row as 107 and 2 at the same row as 40? Hi Aladin No . What I'm saying is that number 3 should be the only number (where it is) and no number 2 because 107 is greater than 35 and that should be the cut off point , there fore the next sequence should start after the #value! in column AU45 . So the next sequence starts at AU 46 where the first number is greater than 35 and that is the cut off point (no result) . The next sequence starts at AU 51 after the #value! in AU50 and number 2 is correct , the cut off point is at AU53 which the number is 146 so no number 3 . The next sequence starts at AU58 after the #value! of AU57. I hope this will help , please don't give up thanks bill  Signaturebill gras > > Hi Aladin, > > Thank you for your time and your reply [quoted text clipped - 21 lines] > Are you trying to say that 3 should appear at the same row as 107 and 2 > at the same row as 40? Let AU38 contain #VALUE!... AV39, copied down: =IF(1-ISNUMBER(AU38),MATCH(TRUE,INDEX(AU39:$AU$500>35,0),0)-1,"") Hope this helps! > I have in column AU cell 39 to cell 500 random sequences of 1-10 rows , > with numbers in them, (as an result of formulas) [quoted text clipped - 27 lines] > thanks > bill gras |
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.