Looking for a Solution (Conditional Array?) - Please Assist

[hrvat2]

Hi.

I "need" to write a function that will populate Col. G values by doing the following:

(Note: Data Set 1 spans columns "A" thru "F", Data Set 2 spans columns "J" thru "O"....below is just a small sample of either)

1) For a given row in column "B", look-up matching values in column "O" and return a sum of corresponding column "K" values (related, Data Set 2);
2) Then multiply the result of the above by the ratio of matching cell's Col. "F" value to that of all relevant Col. "F" values in Data set 1 (essentially pro-rate the above derived total based on Col. "F" values.)

By way of example, the first two rows of Col. "B" (Data Set 1) have a value of "1". Accordingly, Data Set 2,
Col. "O" matching rows (those also containing value of "1") are 1 & 18...and their corresponding values in related Col. "K" are 17.43 for both cells, which sums to 34.86.

Next, the function needs to appropriate that value of 34.86 among Data Set 1 matching (Col. "B" value) rows as based on their Col. "F" ("'Original Duration(h)") values...which in this instance are 6 & 6 respectively (see cells F1 and F2) hence the result for each of these two rows in 17.43 [or (34.86/(6/12))]

Note that Columns "B" and "O" are actually formatted as TEXT (despite their numerical appearance.)

My attempt to get this to work currently looks like this:

=IF(AND(ISNUMBER(SEARCH(B1,O1,1)),MID(O1,FIND(B1,O1)+1,1)<>"."),(SUMIF($O$1:$O$31,B1,$K$1:$K$31)*(F72/SUMIF($B$1:$B$32,B1,$F$1:$F$32))),"")

a) since the two column over which the initial match is performed are text, I am trying to find a sub-string (Col. "B" value) within a text string (Col. "O" value). ISNUMBER(SEARCH()) portion should return a number if there is a match;
b) Since e.g. a value of "4" from Col. "B" matches up with both a "4" and a "4.5" from Col. "O", a second logical test is added via AND(), this test being MID() which confirms that the succeeding text character is not a ".";
c) If the above two conditions are satisfied, simple SUMIF()'s are used to take care of the math.

The above approach does not work, hence this post. I am inclined to think this needs be an array formula (the AND() logical tests operate on a single cell reference context, seems like that might be the issue?) whereas the SUMIFs take an array as their argument). IF anyone can point me in the right direction, I would greatly appreciate it, a huge THANK YOU in advance!!!


THE DATA SET:

Col. A	Col. B	Col. C	Col. D	Col. E	Col. F	Col. G	Col. H	Col. I	Col. J	Col. K	Col. L	Col. M	Col. N	Col. O
Activity Name	Sort	(*)WBS Name	Building	Floor	Original Duration(h)	Hrs			Description	Total Labor Hrs	Bldg	Area	Sub Area
		DATA SET 1							DATA SET 2
Electrical Demo/Remove	1	Interior Demolition/Removal (Smith 2nd)	Smith	2	6	17.43213			DEMO	17.43	Smith	2	Corridor	1
Electrical Riser(s) Demo/Remove	1	Interior Demolition/Removal (Smith 2nd)	Smith	2	6	17.43213			TEMPORARY	17.43	Smith	2	Corridor	2
Temporary Power & Lighting	2	Renovation (Smith 2nd)	Smith	2	6	82.80264			CEILING ROUGH IN POWER	65.37	Smith	2	Corridor	4&5
Wall Cut-Ins for Electric at Demising Walls	3	Residents Rooms (Smith 2nd)	Smith	2	4	25.82538	14.81%		CEILING ROUGH IN DATA	65.37	Smith	2	Corridor	4&5
Fire Alarm Risers	3	Residents Rooms (Smith 2nd)	Smith	2	2	12.91269	7.41%		CABLE TRAY	21.79	Smith	2	Corridor	4.5&5.5
Electrical Risers	3	Residents Rooms (Smith 2nd)	Smith	2	4	25.82538	14.81%		WALL ROUGH	65.37	Smith	2	Corridor	4&5
Data/Comm. Risers	3	Residents Rooms (Smith 2nd)	Smith	2	2	12.91269	7.41%		FIXTURES, DEVICES, CONTROLS	Smith	2	Corridor
Electrical R.I. (Wall & Ceiling)	3	Residents Rooms (Smith 2nd)	Smith	2	5	32.28173	18.52%		FIXTURES, DEVICES, CONTROLS	43.58	Smith	2	Corridor	4.5&5.5
Fire Alarm R.I. (Wall & Ceiling)	3	Residents Rooms (Smith 2nd)	Smith	2	5	32.28173	18.52%		TECH RM - RACKS/LADDER TRAY	108.95	Smith	2	Corridor	4.5&5.5
Data/Comm. R.I. (Wall & Ceiling)	3	Residents Rooms (Smith 2nd)	Smith	2	5	32.28173	18.52%		EQUIPMENT	43.58	Smith	2	Corridor	4&4.5&5&5.5
Electrical R.I. Inspection @ Ext. Wall	0	Residents Rooms (Smith 2nd)	Smith	2	1	FALSE			FIRE ALARM/SECURITY/RADIO/ICOMM	32.69	Smith	2	Corridor	4&4.5&5&5.5
Pull Wire (Power/Data/Comm./F.A./Temp. Control/Etc.)	3.5	Residents Rooms (Smith 2nd)	Smith	2	20	48.74117			PHONE /DATA	21.79	Smith	2	Corridor	4&4.5&5&5.5
Light Fixtures	3.5	Residents Rooms (Smith 2nd)	Smith	2	5	12.18529			CATV	43.58	Smith	2	Corridor	4&4.5&5&5.5
Devices (Power/Data/Comm./Etc.)	3.5	Residents Rooms (Smith 2nd)	Smith	2	5	12.18529			PULL WIRE /TERMINATE	43.58	Smith	2	Corridor	4.5&5.5
Electrical Testing	3.5	Residents Rooms (Smith 2nd)	Smith	2	3	7.311175			FIRE STOP	6.54	Smith	2	Corridor	4&4.5&5&5.5
Fire Alarm Devices	3.5	Residents Rooms (Smith 2nd)	Smith	2	5	12.18529			PRETEST AND FA TESTING	Smith	2	Corridor	4.5&5.5
Wall Cut-Ins for Electric at Demising Walls	4	Core/Corridors (Smith 2nd)	Smith	2	4	0			SMITH 2R	17.43	Smith	2	Residential	3&3.5
Fire Alarm Risers	4	Core/Corridors (Smith 2nd)	Smith	2	2	0			DEMO	17.43	Smith	2	Residential	1
Electrical Risers	4	Core/Corridors (Smith 2nd)	Smith	2	4	0			TEMPORARY	65.37	Smith	2	Residential	2
Data/Comm. Risers	4	Core/Corridors (Smith 2nd)	Smith	2	2	0			CEILING ROUGH IN POWER	65.37	Smith	2	Residential	3
Electrical R.I. (Wall & Ceiling)	4	Core/Corridors (Smith 2nd)	Smith	2	5	0			CEILING ROUGH IN DATA	65.37	Smith	2	Residential	3
Fire Alarm R.I. (Wall & Ceiling)	4	Core/Corridors (Smith 2nd)	Smith	2	5	0			WALL ROUGH	43.58	Smith	2	Residential	3
Data/Comm. R.I. (Wall & Ceiling)	4	Core/Corridors (Smith 2nd)	Smith	2	5	0			FIXTURES, DEVICES, CONTROLS	Smith	2	Residential
Electrical R.I. Inspection @ Ext. Wall	0	Core/Corridors (Smith 2nd)	Smith	2	1	FALSE			FIXTURES, DEVICES, CONTROLS	43.58	Smith	2	Residential	3.5
Pull Wire (Power/Data/Comm./F.A./Temp. Control/Etc.)	4.5	Core/Corridors (Smith 2nd)	Smith	2	15	0			FIRE ALARM/SECURITY/RADIO/ICOMM	32.69	Smith	2	Residential	3&3.5
Light Fixtures	4.5	Core/Corridors (Smith 2nd)	Smith	2	5	0			PHONE /DATA	10.90	Smith	2	Residential	3&3.5
Electric Panels	4.5	Core/Corridors (Smith 2nd)	Smith	2	2	0			CATV	65.37	Smith	2	Residential	3&3.5
Ladder Racks	4.5	Core/Corridors (Smith 2nd)	Smith	2	2	0			PULL WIRE /TERMINATE	43.58	Smith	2	Residential	3.5
Fire Alarm Panels	4.5	Core/Corridors (Smith 2nd)	Smith	2	2	0			FIRE STOP	6.54	Smith	2	Residential	3&3.5
Terminations	4.5	Core/Corridors (Smith 2nd)	Smith	2	10	0			DATA TESTING	4.36	Smith	2	Residential	3.5
Technology Room Work	4.5	Core/Corridors (Smith 2nd)	Smith	2	5	0			FA PRETEST	1.09	Smith	2	Residential	3.5
Fire Alarm Devices	5.5	Bathrooms (Smith 2nd)	Smith	2	2	0

<tbody>
</tbody>

 

[hrvat2]

Anyone have an idea how to make this work?