Count number of items before a blank on a moving range.

[rickywrangler]

Hello,

Daily, I get a pre-generated report with text data in column A. There are blank cells in column A separating the data into categories. Each category has a title, one of them being "Delivery Date." I need to quickly count how many items are in the "Delivery Date" category, but the location of that cell changes every single day. For example, Delivery Date, along with the data below it can start on A6 one day, A16 the second day and maybe even A35 on the third day. It all depends on how many items are under the previous categories. To top it off, the amount of rows under each category also varies day to day.

Truth #1 If Delivery Date starts on A16, I can use MATCH to pinpoint the row where it starts. =MATCH("Delivery Date",A:A,0). This correctly gives me 16.
Truth #2 If Delivery Date starts on A16, I can use ADDRESS & MATCH to pinpoint the exact cell where it starts. =ADDRESS(MATCH("Delivery Date",A:A,0),1,4). This correctly gives me A16.
Truth #3 If Delivery Date starts on A16 and there are 8 dates underneath it and then a blank, I can use MATCH & TRUE to count those dates. =MATCH(TRUE,(A16:A1000=""),0)-2. This correctly gives me 8.

Since day to day I won't know to start the search on A16, I'm trying to substitute the A16 from truth #3 with the ADDRESS & MATCH result from truth #2, but it's not working. Is there a way to accomplish this? Like always, in my head it sounds easy: find where Delivery Date starts (different each day) and then count how many items are under it until the next blank (different each day).

Easy peasy I thought, but I'm stumped. The attached image is a sample but the actual spreadsheet will have hundreds of items under each category. Thanks in advance for your time.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this formula

=LET(DDr,MATCH("Delivery*",A:A,0),AGGREGATE(15,6,ROW(A1:A1000)/((A1:A1000="")*(ROW(A1:A1000)>DDr)),1)-DDr-1)

 

[Peter_SSs]

.. or if Receipt Date group always follows Delivery Date group then try

=MATCH("Receipt*",A:A,0)-MATCH("Delivery*",A:A,0)-2

 

[mohadin]

Hi
What about

Sub test()
Set myarea = Cells(1).SpecialCells(2, 23).Areas
    For Each ar In myarea
        If ar(1) = "Delivery Date" Then
         MsgBox ar.Count - 1
        End If
    Next
End Sub

 

[Peter_SSs]

Hi
What about

For a vba approach, with nothing in column B per the original image, this should suffice

Sub CountDeliveryDate()
  MsgBox Columns("A").Find(What:="Delivery Date").CurrentRegion.Count - 1
End Sub

 

[rickywrangler]

Wow, thank you both! I'm excited to try these out when I get home from work.

 

[rickywrangler]

.. or if Receipt Date group always follows Delivery Date group then try

=MATCH("Receipt*",A:A,0)-MATCH("Delivery*",A:A,0)-2

This solution was magnificent in it's simplicity and creativity. I was so concerned with Delivery Date that it didn't occur to me to just calculate Receipt Date (which does always follow Delivery Date) and subtract from there. I'm so happy. Thanks.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.