Count cells from left to right based on > value

Hello Forum,

Thank you in advance for your help.

I'm looking for some help writing this formula or VBA script. I have a report consisting 10,000 records and for each line, I'm trying to calculate a consistent value count from left to right when the score is greater than a set number. IE: 94 in this example.

I'm looking for a formula or script to count based on a number greater than 94 starting from cell A1, then to A2, and so on and stopping when the value is less than the 94.

See below

Example #1 Expected result = 3
A1=96
B1=98
C1=100
D1=79
E1=99
F1=97

Example #2 Expected result = 2
A1=96
B1=98
C1=27
D1=99
E1=99
F1=97


Thanks again for your help

Sorry, here ya go. In Example 1 the formula will look at cell A1 and if the value is > 94 it will count as 1 and proceed to next cell (B2). If B2 is greater than 94, it will count as 2, then looks at C1 and if greater than 94, will count as 3. When it hits cell D1 the value is less than 94 so it stops counting. Cells E1 and F1 are greater than 94, but we do not want to include. So basically it will count each value from left to right and will not stop until it hits a value that is greater than 94. Hope this help. Thank you so much

This is excellent! Thanks so much. I'm sorry, I should have added some more info.

How could I create a formula for each row? So if I had 100 lines of data and for each line on column G it would have the result for each line.

I think this should work:

=MATCH(94,A1:F1,-1)

depending on if you wanted to stop counting when it was exactly 94, you may want to enter in 93.99999 or 94.0001

Ok got it. Tested and works perfect! Thank you so much! You rock

Sorry Greg, just saw your post. Thank you too!

Originally Posted by alansidman

Here is a VBA solution

Please Login or Register  to view this content.

How to install your new code

• Copy the Excel VBA code
• Select the workbook in which you want to store the Excel VBA code
• Press Alt+F11 to open the Visual Basic Editor
• Choose Insert > Module
• Edit > Paste the macro into the module that appeared
• Close the VBEditor
• Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

• Press Alt-F8 to open the macro list
• Select a macro in the list
• Click the Run button

Thanks for your Help on this one. I have saved this one and this will also come in handy.