Countif or something equivalent to count items based on relative position in a rack map

I have a very simple rack layout in excel where the each Row represents one Rack unit. Each Column of 45 Cells represents a Rack. Individual cells are merged based on the Rack U height and placed in the layout.

Racks are 45 U high
Devices 1 U in height get 1 cable
Devices with 2 U in height get 2 cables
and devices that are larger, get 4 cables

I'm using cables that are 3, 4, 5, 6, 8, and 10 feet long to reach the switches that they connect to.

Each device in the rack is simply labeled "R320-xxx" or "R720-xxx" to denote the type of device, and the future project number. I'm simply trying to get a count of each length of cable needed based on the relative position of the devices in the racks. These racks span across two rows, so Row A has 10 racks, Row B has 10 Racks.

I've been using "3 foot" =(COUNTIF(B3:B10,D1)*2)+(COUNTIF(B3:B10,E1))+(COUNTIF(D3:D10,D1)*2)+(COUNTIF(D3:D10,E1))...... and on and on.

This gives me the information that I need but it's painful. Needless to say, I have to use the Countif on a range in the relative position zone that I have defined, for each and every type of device (1U 2U or 10U) and each rack, resulting in just one formula having to use Countif 60 times or more depending on the number of racks I have. Then having to repeat this formula for each length of cable with different ranges. While this works it's incredibly painful. The reason it is painful, is that each rack in-between has a slightly different range because they need to reach the rack next to them. Is there an easy way to do this, and use it in a template, so I can simply specify the ranges, that each criteria belongs to, and add them up to get the correct count?

I've tried different things including the following that all return #VALUE
=Sum(If(Range,Criteria(If(Range, Criteria)0,1)))... =#VALUE
=SUMPRODUCT <---Confusing =#VALUE
=(COUNTIFS(range1, criteria1, range2, criteria1))+(COUNTIFS(Range1, criteria2, Range 2, criteria2)*2)... =#VALUE

I will have to sanitize it a bit. I'll post it soon. Thanks.