Count Number of Cells Until a Value is reached

[yabastud]

I'm using Excel to track donations. Each Row is a different donor. Each column is a year. 1976-2016. I'm trying to count how many donations they made before their first donation of $1,000 or more in a separate column.

[Tony Valko]

Try something like this...

Data Range

	A	B	C	D	E	F	G	H	I
1	------	1976	1977	1978	1979	1980	1981	1982	1983
2	5	500	500	750	750	1000	1200	1500	2000

This array formula** entered in A2:

=IFERROR(MATCH(TRUE,B2:I2>=1000,0),0)

** 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.

[yabastud]

Thank you Tony. This is really close to what I need. However, how would I get it to exclude blank cells?

[Tony Valko]

Like this...

Data Range

	A	B	C	D	E	F	G	H	I
1	------	1976	1977	1978	1979	1980	1981	1982	1983
2	3	500	500			1200	1200	1500	2000

This array formula** entered in A2:

=IFERROR(MATCH(TRUE,B2:I2>=1000,0)-COUNTIF(B2:INDEX(B2:I2,MATCH(TRUE,B2:I2>=1000,0)),""),0)

** 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.

[yabastud]

Thank you, Tony. This is really close to what I need. However, how would I get it to exclude blank cells?

[Phuocam]

Or try this ...

=IFERROR(COUNT(B2:INDEX(B2:I2,MATCH(TRUE,B2:I2>=1000,0))),0)

Enter with Ctrl+Shift+Enter.

[Tony Valko]

Or try this...

Data Range

	A	B	C	D	E	F	G	H	I
1	------	1976	1977	1978	1979	1980	1981	1982	1983
2	3	500	500			1200	1200	1500	2500

=COUNT(B2:INDEX(B2:I2,MATCH(TRUE,B2:I2>=1000,0)))

Still array entered.

[yabastud]

Tony, that was the answer I needed. Thank you so much.

[Tony Valko]

You're welcome. Thanks for the feedback!