Sum if named range begins with

Hi,

I have traffic junctions flow data in 2 sheets: AM_Flows and PM_Flows.
Both sheets contain the traffic flow through each arm of each junction.
I have used named ranges to reference the time period (AM or PM), the highway junction code (eg. J3, J29, J35A etc.) and the junction arm (A,B,C or D).
So for example, named cell reference AM_J30_A contains the morning flows from Junction 30 arm A.
AM_J30_A + AM_J30_B + AM_J30_C + AM_J30_D = the total morning flow through Junction 30.

In a separate sheet, I have a table that needs to calculate the total flows through each junction (i.e. the sum of flows through every arm of a given junction) for each time period.

The formula would need to be dynamic in 2 ways:
- the table's column headers are AM and PM, so the column of a given cell in the table determines which set of named ranges are SUMed (e.g. AM_J30_A vs. PM_J30_A)
- The table's row headers are the junction codes, so the row of a given cell in the table again determines which set of named ranges are SUMed (e.g. AM_J30_A or AM_J31_A etc).

It seems that I can't use a SUMIF(INDIRECT(Period_Junction_*) i.e. sum any named range which starts with the desired time period and junction code.

Any help would be greatly appreciated. See attached.

Please add expected results for a couple of junctions.

and VBA as a possible solution?

Please Login or Register  to view this content.

See here (as a start)

https://excelchamps.com/vba/dim/

"Dim a,b" will default to atribute of variant: you other variables have their attributes defined (Integer, Long etc)

This ..

Please Login or Register  to view this content.

assigns the range B3:E (to last row) into "a" (which an array): array processing is much faster than read/write to cells

Looking at the code "b" is redundant obviously changed my thinking when developing the code)!

Out of interest, I renamed (some of )the junctions by adding a prefix of "J" (J7AB, J34CD ...) and added code which did the calculation by referencing the junctions rather than using named ranges.

I think this is possible: I currently do a "wildcard" search for a junction e.g. J34" but this will still allow for the omission of certain junction codes.

See attached example: "Sheet "Output" with input from "AM Flows test". 2 Junctions 7 & 35

Please Login or Register  to view this content.