I am stuck with developing a formula that would count total ride number, which start in one neighborhood and end in another. There are 83 neighborhoods in total to have data counted for, but each of them is split into several zones, where each of them has unique public number attached to them. For example, "Neighborhood A" consists of zones 5431, 5432, 5434 and 5435; then "Neighborhood B" consists of zones 2312, 2333, 2385; "Neighborhood C" consists of zones 1678, 2343, 3761 and so on. One important aspect to remember here is that there is no specific number of zones for a neighborhood. The number varies, but it's value belongs to a single range (further I will refer to it as a Start_range or Finish_range).
The output result I require is a table, that has these columns: Table example
The zones for every neighborhood are specified on a separate worksheet in the same workbook. The specification table looks like this: Zone arrangement table example
What is the biggest concern in this whole situation is how can I set Excel understand, that the string value (e.g. "Neighborhood A") that it reads from cells in the first column and in the forth column is referred to the array of zones, and also that the array of zone values means that it needs to count the total number of rides between all the zones of both neighborhoods. To make it absolutely clear, I mean that:
Neighborhood A -> Neighborhood C means count total number from rides
2435 -> 2066
2435 -> 3044
2435 -> 2043
2162 -> 2066
2162 -> 3044
2162 -> 2043
(see picture "Zone arrangement table example" for reference).
All the zones are listed in a separate worksheet in a different workbook. The lookup range is one column and 62579 rows. Each row contains a number of a specific zone. There are 2 columns like this - one for Start zone (Start_zone_range) and another for Finish zone (Finish_zone_range).
There are 2 additional arguments in the statement - transport mode and day. They do not cause any trouble, just need to be noted for understanding the statement. The ranges for them are listed in the same workbook and worksheet as the ranges for zones (Transport_mode_range and Day_range). Both of these ranges only contain string values. Each range is one column and 62579 rows (equal to every other argument).
My current formula statement returns 0, but is set like this:
=COUNTIFS('[Data_workbook]Data_worksheet'!$AS$2:$AS$62579 (Start_zone_range);A2 (1,1 cell in the output table (see "Table example");'[Data_workbook]Data_worksheet'!$AT$2:$AT$62579 (Finish_zone_range);D2 (1,4 cell in the output table (see "Table example");'[Data_workbook]Data_worksheet'!$T$2:$T$62579 (Transport_mode_range);"Auto" (Transport_criteria);'[Data_workbook]Data_worksheet'!$M$2:$M$62579 (Day_range);"Tuesday" (day_criteria).
I tried solving it through VBA. After parsing several similar threads, I found some examples to do, but I really am not experienced in VBA. It didn't work out, but I will explain, which steps have I taken. So I started with trying to establish in public sub that range "Neighborhood A" is equal to Range of values from the worksheet, that has zones specified for each neighborhood ("Legend"). It went like this:
Public Sub define_ranges()
Dim Range("Neighborhood A") As Variant
"Neighborhood A" = Array(Worksheets("Legend").Range("$B$1:$B$2"))
Dim Range("Neighborhood B") As Variant
"Neighborhood B" = Array(Worksheets("Legend").Range("$B$3:$B$17"))
Dim Range("Neighborhood C") As Variant
"Neighborhood C" = Array(Worksheets("Legend").Range("$B$18:$B$34"))
Dim ("Neighborhood D") As Variant
"Neighborhood D" = Array(Worksheets("Legend").Range("$B$35"))
And so on...
End Sub
Then, after defining ranges for all the 83 neighborhoods, I went on to define a countifs() loop cycle, which I spied from another thread here.
It went on like this:
Sub do_countifs_loop()
Dim lastRow As Long
Dim i As Double
Dim WS As Excel.Worksheet
Dim lRow As Long
Dim StartValuesRange As Range
Dim FinishValuesRange As Range
Dim ResultRange As Range
Dim StartCriteriaValue As String
Dim FinishCriteriaValue As String
Dim DayCriteriaValue As String
Dim DayCriteriaValueRange As Range
Dim TransportCriteriaValue As String
Dim TransportCriteriaValueRange As Range
DayCriteriaValue = "Tuesday"
TransportCriteriaValue = "Auto"
Set WS = ActiveWorkbook.Sheets("Auto rides on Tuesday")
lastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
Set StartValuesRange =
Range(Workbooks("Data_workbook").Worksheets("Data_worksheet").Range("$AS$2:$AS$62579"))
Set FinishValuesRange =
Range(Workbooks("Data_workbook").Worksheets("Data_worksheet").Range("$AT$2:$AT$62579"))
Set DayCriteriaValueRange =
Range(Workbooks("Data_workbook").Worksheets("Data_worksheet").Range("$M$2:$M$62579"))
Set TransportCriteriaValueRange =
Range(Workbooks("Data_workbook").Worksheets("Data_worksheet").Range("$T$2:$T$62579"))
lRow = 2
WS.Activate
Do While lRow <= lastRow
StartCriteriaValue = WS.Range("A" & lRow)
FinishCriteriaValue = WS.Range("D" & lRow)
i = Application.WorksheetFunction.Countifs(StartValuesRange, StartCriteriaValue, FinishValuesRange, FinishCriteriaValue, DayCriteriaValueRange, DayCriteriaValue, TransportCriteriaValueRange, TransportCriteriaValue1, TransportCriteriaValueRange, TransportCriteriaValue2)
WS.Range("B" & lRow).Value = i
lRow = lRow + 1
Loop
End Sub
But yeah, didn't work out as well, unfortunately. Received both some code issue errors, fixed them somehow, but then received Error 1004 "Application-defined or Object-defined error".
So, here I am, trying to figure out, what is going wrong. I will be highly thankful for any input, tip and piece of advice, as I am having hard time figuring this out for several sessions already.
N.B.1.: I am also capable of little Python work, so if there is a more straightforward way that you know that might be done via Python, please let me know.
N.B.2.: Sorry for a long read. It is my first thread here, so I am yet to figure out whether explaining needs more or less details.
