+ Reply to Thread
Results 1 to 9 of 9

How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it further?

  1. #1
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it further?

    The formular =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) only works once and then only gives me zeros, How can I change it so that it works on 4200 rows?
    Last edited by Dussy; 05-26-2017 at 07:25 AM.

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    What are you trying to achieve?
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  3. #3
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    As you can see there is the row for event A and Event B. I would like to tell excel to start a counter if it sees a 0 in row (Event A) and carry on counting until it sees a 0 in row (Event B). If it starts the counter it should ignore any 0 in row (Event A) until it recognizes a 0 in row (event B) and stop counting upon the 0 in row (event B) and then look in row (event A) for another 0. However as you can see a21 has a zero and the counter shows a zero and does not carry on counting until it finds the next 0 in b24. so it basically only works once and not many times over if i want to pull the formula down on 4200 rows
    Attached Images Attached Images
    Last edited by Dussy; 05-26-2017 at 08:18 AM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,712

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    It would be helpful if you could show in column D what you are hoping to achieve. Is it a series of 1's, then a series of 2's, then 3's etc. governed by zero in column A to start a new number and zero in B to stop the numbering?

    Pete

  5. #5
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    I have a list of 4200 RSI reading (RSI is a technical indicator used in trading) and there is an upper boundary of 90 and a lower boundary of 10, I would like to find the average time it takes for the RSI to do one full oscillation meaning being above 90 going down to 10 and back up to 90.
    In the previous screenshot the 0 in column A denotes when the RSI is above 90 and column B When the RSI is Below 10. Which would indicate half an oscillation then I thought I would reverse the solution and then make a counter find out how long it takes per oscillation find the total number of oscillations and then work out the average time period and thus frequency.
    I have attached a copy of the spreadsheet maybe you guys have another approach to solving this problem.
    Attached Files Attached Files

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,028

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    In T3 then drag down:

    =SUMPRODUCT(--($R$2:R3+$S$2:S3=1))

    Are you using lot of helper columns to refer to RSI (column H)?

    If yes, I will try to use direct solution to RSI idexes to avoid helpers.

  7. #7
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    Hey Thanks for your quick response. Yes I am because I tried to find out other variables aswell. I inserted your formular and it works to the extent that it counts one more when ever there is a zero. However when there is a 0 in column R (Event A) it starts counting and when there is a second zero in R it counts one more whereas it shoulndt because it should ignore this 0 until it reaches a in clomn S (event B) as it would other wise not be a full oscilation. As RSI would be above 90 below 90 for 3 days and then above 90 again however not below 10 which would be half and oscilation. How can I get excel to count upon the first 0,lets call it 0a in R then ignore the following zeros until it reaches a 0 in S (0c) then stop counting until it finds the next 0 in R and then to count again until it reaches a 0 in S regardless of any 0 in R between 0a and 0c.

  8. #8
    Registered User
    Join Date
    05-26-2017
    Location
    London
    MS-Off Ver
    MS2016
    Posts
    6

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    If you look in the spreadsheet column H Shows the RSI Value, How could you count the number of rows it takes (each row 1 day) for the RSI to go from above 90 to below 10 and back up to 90?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,300

    Re: How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it furth

    In AB9 (or first cell where H > 90) put "A"

    in AB10 (or cell following Above)

    =IF(AND($H10<10,LOOKUP(2,1/($AB$2:$AB9<>""),$AB$2:$AB9)="A"),"B",IF(AND($H10>90,LOOKUP(2,1/($AB$2:$AB9<>""),$AB$2:$AB9)="B"),"A",""))

    and copy down

    you will get pattern of "A", "B", "A" i.e >90, then <10 , > 90

    in AC2

    =IF(AB2<>"",COUNTIF($AB$2:AB2,AB2),"")


    will count number of "A"s and "B"s

    Hopefully this will help in getting your results
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. I can't make this countif equation work. Help
    By Holden Wright in forum Excel Tips
    Replies: 5
    Last Post: 02-04-2016, 06:30 AM
  2. Faster if/countif formula for flagging uniques - countif too slow
    By Speshul in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 01:44 PM
  3. how to make countif case sensitive
    By vsbhogar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-19-2012, 02:02 PM
  4. Count() and CountIf() results don't make sense.
    By foxguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2012, 04:59 AM
  5. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM
  6. Modify COUNTIF to make it case sensitive
    By singhabhijitkumar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2010, 06:26 AM
  7. [SOLVED] How do you make a countif formula with 2 or more critera?
    By Melissa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2005, 08:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1