Formula that counts specific values, but stops at the point that the value changes.

[AlphaRaveNZ]

Is there a formula that will count the number of rows that contain a value (i.e. count the rows that have an "L" in column A) but will stop the total as soon as the value changes in the first instance.

I.e. (column b would be the formula in the below example)

Please Login or Register  to view this content.

[Tony Valko]

Assuming the sequence always starts with a "P"...

Data Range

	A	B
1	Sequence	Count
2	P	7
3	L
4	L
5	L
6	L
7	L
8	L
9	L
10	P	5
11	L
12	L
13	L
14	L
15	L
16	P	2
17	L
18	L
19

This array formula** entered in B2 and copied down as needed:

=IF(A2="P",COUNTIF(A3:INDEX(A3:A$19,MATCH(TRUE,A3:A$19<>"L",0)),"L"),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note how the formula refers to 1 cell beyond the last cell that contains data.

[AlphaRaveNZ]

Thanks, this worked perfectly!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.