Count the number of cells until a value is reached w/multiple criteria

[Degausser124]

Hi!

First, big thanks to everyone who helps on this forum. I reference this board at least weekly and I'd be lost without you all.

Attached is a dumbed down version of my file. What I'm looking to do is:

• Count the number of cells in the range D:K starting with Row 3 where the following 2 conditions are met:
  • Start counting in the during the starting week (Column B)
  • AND Stop counting when the headcount (Column A) is achieved
• Column C is where the formula will live

Example:

• Row 3: I want to know how many weeks it takes to meet or exceed 100 headcount (A3), starting with week 2 (B3)
  • Answer: Should be 5 because it starts with E3, counts that week and stops at I3

I'm pretty sure this is a combo of a COUNTIFS and Index Match but for some reason I just can't get it right.

Thanks so much!

 

[KRice]

try this...

MrExcel_20220614.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Headcount	Start Wk	Wks to Reach	1	2	3	4	5	6	7	8
3	100	2	5	20	40	60	80	90	100	120	150
4	120	4	2	30	40	70	100	150	180	220	290
Sheet8

Cell Formulas
Range	Formula
C3:C4	C3	=MATCH(1,--(D3:K3>=A3),0)-B3+1

 

[Degausser124]

try this...

MrExcel_20220614.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Headcount	Start Wk	Wks to Reach	1	2	3	4	5	6	7	8
3	100	2	5	20	40	60	80	90	100	120	150
4	120	4	2	30	40	70	100	150	180	220	290
Sheet8

Cell Formulas
Range	Formula
C3:C4	C3	=MATCH(1,--(D3:K3>=A3),0)-B3+1

Thank you @KRice, this works!

 

[KRice]

You're welcome...happy to help!

 

[Degausser124]

You're welcome...happy to help!

@KRice One minor wrinkle. There are cases where the Headcount is already met at the starting week and the headcount decreases.

I included an example in Row 6. Using the above formula formula the output is a -3. I'm hoping there's a way to tweak the formula to recognize that the headcount was met on week 5 and output a 1.

Would that be possible? Thanks again, I really appreciate it!

 

[KRice]

Oh...I had not anticipated that. So are you saying then that this would be an exception to the rule? That is, if you adhere strictly to the rule and begin counting at the "start week" of 5 (for the row 6 case), you'll never reach the target headcount. So you want a week number returned in those instances, even if it is before the "start" week?

=LET(cidx,MATCH(1,--(D3:K3>=A3),0),IF(cidx<B3,cidx,cidx-B3+1))

Also, what should happen if the headcount is never reached?

 

[KRice]

To illustrate my question, consider the following options. The green formulas return the week at which the headcount is reached IF that week occurs prior to the "start" week. The blue formulas return a "1" instead, indicating that by the time we begin counting at the start week (even if the value in that start week is too small), we've already passed the actual week where the headcount was reached. The orange formula is the blue formula except an extra error trap is added to address the case where no match is found, probably because the headcount is never reached.

MrExcel_20220614.xlsx
	A	B	C	D	E	F	G	H	I	J	K
7	Headcount	Start Wk	Wks to Reach	1	2	3	4	5	6	7	8
8	30	5	1	50	40	30	25	20	15	10	5
9	30	5	3	10	20	30	40	50	60	70	80
10	30	5	1	50	40	30	25	20	15	10	5
11	30	5	1	10	20	30	40	50	60	70	80
12	90	5	not found	10	20	30	40	50	60	70	80
Sheet8

Cell Formulas
Range	Formula
C8:C9	C8	=LET(cidx,MATCH(1,--(D8:K8>=A8),0),IF(cidx<B8,cidx,cidx-B8+1))
C10:C11	C10	=LET(cidx,MATCH(1,--(D10:K10>=A10),0),IF(cidx<B10,1,cidx-B10+1))
C12	C12	=IFERROR(LET(cidx,MATCH(1,--(D12:K12>=A12),0),IF(cidx<B12,1,cidx-B12+1)),"not found")

 

[Degausser124]

Oh...I had not anticipated that. So are you saying then that this would be an exception to the rule? That is, if you adhere strictly to the rule and begin counting at the "start week" of 5 (for the row 6 case), you'll never reach the target headcount. So you want a week number returned in those instances, even if it is before the "start" week?

=LET(cidx,MATCH(1,--(D3:K3>=A3),0),IF(cidx<B3,cidx,cidx-B3+1))

Also, what should happen if the headcount is never reached?

Your revised formula worked! I'll add an IFERROR if the headcount is never reached to leave the cell blank.

Thanks again, this helped immensely!

 

[KRice]

Good...so did you want the green version or the blue version?...one returns the column index, the other returns a "1" for the exceptional cases.

 

[Degausser124]

Good...so did you want the green version or the blue version?...one returns the column index, the other returns a "1" for the exceptional cases.

Thie formula below works great. I'll spot check again but I think that solves what I'm looking to achieve.

=LET(cidx,MATCH(1,--(D3:K3>=A3),0),IF(cidx<B3,cidx,cidx-B3+1))