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]

OK, good...that's the version that returns the column index (week number) where headcount is met if it occurs before the "start" week...otherwise, the formula returns the number of weeks counted beginning with the start week up until the headcount is met.