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.
-
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.– Community BotJun 5, 2022 at 8:15
-
For Time... did the value count for each cell?– LeeJun 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.– Ranjit Singh ShekhawatJun 7, 2022 at 8:31
1 Answer
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)
-
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