0

I have a list of values and want to count how many times values exceeds 400, but if next row values also match this criteria this should not be in counting. COUNTIF formula is of no use in this case I suppose. Please see the attached picture for better understanding.

enter image description here

3
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Jun 5, 2022 at 8:15
  • For Time... did the value count for each cell?
    – Lee
    Jun 6, 2022 at 9:41
  • I want final count value at bottom of VALUES column to be 3. As the values has gone above 400 only 3 times in group. Jun 7, 2022 at 8:31

1 Answer 1

0

enter image description here

In this image I've put your criteria value in cell A2.

In columns E through H, the formulas are entered in the first row of data (row 3 in the spreadsheet) and then copied down to the bottom of the range.

In column E is a formula to get the value from the previous row:

=IF(ISTEXT(B2),0,B2)

In column F is a formula to calculate whether the value on the current row (in column B) is greater than the criteria in cell A2 and the value from the previous row (now in column E) is less than or equal to the criteria in cell A2. This formula returns a 1 in a row where a group of rows greater than the criteria begin:

=IF(AND(E3<=$A$2,B3>$A$2),1,0)

In column G is a running sum of the value in column F:

=SUM($F$3:F3)

Note that the first reference in the range is anchored with absolute references. As this formula is copied down, the range it is summing will grow, leading to the values shown in column G.

Finally, in column H, we show the value from column G if the value in column B is greater than the criteria in cell A2:

=IF(B3>$A$2,G3,"")

If you have the latest version of Office 365, you can combine all steps into a single formula, which is entered only in cell I2 in the image above:

=LET(
base,$A$2,
data,$B$3:$B$12,
prev,OFFSET(data,-1,0),
numprev,IF(ISTEXT(prev),0,prev),
grpchg,BYROW(CHOOSE({1,2},data,numprev),LAMBDA(r,IF(AND(INDEX(r,,1)>base,INDEX(r,,2)<=base),1,0))),
runsum,SCAN(0,grpchg,LAMBDA(a,b,a+b)),
output,IF(data>base,runsum,""),
output)
1
  • Thank you for the solution. I actually want final count value at bottom of VALUES column to be 3. As the values has gone above 400 only 3 times in group. In case of your image it should be at B13. Could you please see it. I can't create more column in my excel sheet as the data is very big. Jun 7, 2022 at 8:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .