How to continually count if cell value(s) in a range are greater than x?

[danbates]

Hi,

Please can someone help me?

My range is C7:C737 and I have a value of 10 in cell C5.

What I would like is to be able to continually count if the values in the range that are greater than the value in C5 but if one value is less or equal then the count goes back to 0.

Also there will be blank cells in the range that will need to be ignored.

Any help would be appreciated.

Thanks

Dan

 

[footoo]

=IF(MIN(C7:C737<=C5,0,COUNTA(C7:C737))

 

[danbates]

Hi,
Thank you for your reply.

It is saying it is missing a parenthesis, any ideas?

Thanks

Dan

 

[footoo]

=IF(MIN(C7:C737)<=C5,0,COUNTA(C7:C737))

 

[danbates]

Hi,

Unfortunately it's not working, it's not counting the higher values.

Thanks

Dan

 

[footoo]

Post a few samples of data with the results you want.

 

[danbates]

2	2	11
11	2	11
2	2	11
11		"blank"
1	2	"blank"
11	2	11
11	2	11
11	11	11
11	2	"blank"
Count should be 4	Count should be 0	Count should be 6

Numbers are just an example and the "blank" cells will have nothing in them.

Hope this helps.

Thanks

Dan

 

[danbates]

Is it possible to convert the following VBA code into a formula?

If Range("G7") > Range("C5") Then
Range("G10").Value = Range("G10") + 1
Else
Range("G10").Value = 0
End If

I have this formula in cell G7 as a helper cell to find the last entry
{=INDEX(C:C,MAX((C:C<>"")*(ROW(C:C))))}

 

[footoo]

If the use of a helper column is acceptable (say,column F), put in F7 and fill down :

=IF(G7="",F6,IF(G7<=C$5,"0",IF((G7>C$5)*((F6="")+(F6=0)),1,F6+1)))

There is probably a way (beyond my knowledge) of avoiding a helper column and with a formula in one cell.

 

[Eric W]

If I understand correctly:

Book1
	C	D	E	F	G	H	I
5	10				4	0	6
6
7	2	2	11		15	15	14
8	11	2	11
9	2	2	11
10	11
11	1	2
12	11	2	11
13	11	2	11
14	11	11	11
15	11	2
16
Sheet10

Cell Formulas
Range	Formula
G5:I5	G5	=IF(INDEX(C:C,G7)<$C$5,0,MAX(FREQUENCY(IF(C7:INDEX(C:C,G7)>$C$5,ROW(C7:INDEX(C:C,G7))),IF((C7:INDEX(C:C,G7)<>"")*(C7:INDEX(C:C,G7)<=$C$5),ROW(C7:INDEX(C:C,G7))))))
G7:I7	G7	=LOOKUP(9E+307,C:C,ROW(C:C))
Press CTRL+SHIFT+ENTER to enter array formulas.

The G7 formula gets the bottom row of the range. Then the G5 formula should give you the count you're looking for. I just put your other examples in D and E, and dragged the G formulas to the right, to show that the formula works on the 3 examples you gave. Nothing in D, E, H, or I is needed. In fact, you don't even need the G7 formula to be separate, you could incorporate it into the G5 formula, but since it's used 6 times, it'd make that formula quite long. This would be a good use for the new LET function.