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: 14

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
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)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
.. 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
 
Solution

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,393
Office Version
  1. 2013
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi
What about
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
 

rickywrangler

New Member
Joined
Dec 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Wow, thank you both! I'm excited to try these out when I get home from work.
 

rickywrangler

New Member
Joined
Dec 4, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
.. 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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,463
Messages
5,831,783
Members
430,088
Latest member
meagerd

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
Top