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

rickywrangler

New Member
Joined
Dec 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
    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)
 
Upvote 0
.. 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
 
Upvote 0
Solution
Hi
What about
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
 
Upvote 0
.. 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.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top