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

#### rickywrangler

##### New Member
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.

#### Attachments

• Delivery Date Items.jpg
92 KB · Views: 22

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the MrExcel board!

Try this formula

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

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

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

Hi
VBA Code:
``````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``````

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

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

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

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

Excel Formula:
``=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.

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

Replies
5
Views
240
Replies
3
Views
331
Replies
8
Views
825
Replies
2
Views
590
Replies
0
Views
614

1,218,748
Messages
6,144,274
Members
450,533
Latest member
xoxo1998

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back