Combining AND OR with COUNTIF

[robwags]

Hi,

I am trying to analyze data using COUNTIF to see how many times a certain row goes above the value .02 at the same time as 25% of the column does that. I have managed to write a formula that does that but now I have an issue. Several times in the data the row will be above the .02 value for multiple cells in a row and I do not want the subsequent cells counted but cannot figure out how to do that. There are also times where the cell before is at .02 but the column is not 25% and vice versa.

I've attached a sheet with an example of the data and what I've been trying to do. Any help would be greatly appreciated. Thanks!

[modytrane]

I created a helper Row and entered following in C6, copied across that to BE6.

Then used the

formula. The answer is 9.

[robwags]

Thanks! That seems to work pretty well but when I tried a row of values it skips one at EE9. I attached the updated sheet. I don't see a reason for it not to count.

[modytrane]

Because the previous one at ED9 is also above 0.02

Same way ET9 is not counted because ES9 is above 0.02.
And FE9 isn't counted either.

Maybe I missed the logic you want to use.
I think you want to count the first time Row 9 goes above 0.02 and Row 10 is above 0.25.
So use this formula instead.
In C11:

Drag it across. The result is 16.

[modytrane]

You can use this formula without any helper Row.

Use this anywhere, without anything else in Row 11.