Formula Dynamic range between data

Question

I need a formula that can make a range expand in between other data.
In column B for each month you can put either Foo or Bar as values.
I need to count the number of foos and bars in each month. But also make it possible to expand the range from B6:B11 to for example B6:15 and the count function should work.

=COUNTIF(B6:B11,"Foo")

But that isn't dynamic.
I know you can make dynamic ranges with OFFSET and COUNTA but that doesn't work because the data is not structured the way this formula needs it to be.

OFFSET($A$1,0,0,COUNTA($A:$A),1)

I made it making a VBA UDF which emulates the CTRL+DOWN to find the next filled cell, but because the count of Foos or Bars are later linked to a different master workbook a UDF will not work, UDFs can't run with linked values. (as far as I know)

=COUNTIF(INDIRECT("B"&nextC(A5)&":B"&last(A5)),"Foo")

where nextC and last is:

Function last(rng)
    last = Sheets("Sheet1").Cells(rng.Row, "A").End(xlDown).Row - 1 ' finds the last cell row in the current month
    ' with A5 as input it returns 11
End Function

Function nextC(rng)
    nextC = rng.Offset(1, 0).Row ' returns 6 if input is A5 
    ' there may be a better way to do this, I just couldn't think of it at the moment and just wanted to see if it worked
End Function

Is there any formula that can replicate the UDF meaning give COUNTIF a dynamic range that is in between data as the picture shows.
If helper columns are needed then it's not a problem.

JvdV JvdV · Accepted Answer · 2019-06-05T07:08:12

This should do the trick:

Formula in D2:

=COUNTIF(INDEX($B:$B,MATCH($C$1,$A$1:$A$1000,0)+1):INDEX($B:$B,MATCH(FALSE,INDEX($A:$A,MATCH($C$1,$A$1:$A$1000,0)+2):$A$1000="",0)+MATCH($C$1,$A$1:$A$1000,0)),D1)

Enter through CtrlShiftEnter

And drag to E2....

Result when adding new lines:

Change the A1000 range to whatever you need in your case. It's just there to make sure you surpass the last row, where I have put a string 'End' to mark the last row.

Formula Dynamic range between data

1 Answers