Altering a match/index formula

[KollBrian]

Hey all, I need to alter an index/match formula to work with rows instead of columns.

Here is the original formula:
=INDEX('Loads'!AL3:AL185,MATCH(A3,'Loads'!A3:A185,FALSE),1)

below are the 2 examples of where I need to put the new formula and where it needs to look.

Month End Compilation Report.xls
	A	B	C	D
45	DaveCrosby'sTeam	TotalLoadsPickedUpYesterday	TotalPickupsLateYesterday	PickupOntime%
46
47	Benefiel,Krissa	1	1	0.0%
48	Bowker,Dave	1	1	0.0%
49	Briceno,Alma	1	1	0.0%
50	Crooks,Holly	1	1	0.0%
Printable Report

Month End Compilation Report.xls
	A	B	C	D	E	F	G
81		Abriceno	Abriceno	Acontreras	Acontreras	Aoverby	Aoverby
82		PU	Del	PU	Del	PU	Del
83		1	5			8
84		2		5			5
85		3			47
86	Totals	6	5	5	47	8	5
Actual Late Loads

So, what I am trying to do is match the name in column "I" (the abreviated name) and the determining code (i.e. "pu" or "del") with the name and determining code in another worksheet, then give the total at the bottom of the sheet.

So that if the formula is for the loads picking up the formula looks for abriceno, then pu, then gives the total at the bottom of that column.....???

I am going to start hammering at this trying to see if I can figure it out, maybe a dual match... not sure yet, but if any of you have any thoughts, let me know.

bk

 

[Aladin Akyurek]

The figure below shows how to compute the desired sum for the compound condition of a person and a qualifier (e.g., {"Acontreras","Del"}) using relevant definite ranges or relevant ranges referred to by a name which is defined using a dynamic formula.

Book3
	A	B	C	D	E	F	G
80
81		Abriceno	Abriceno	Acontreras	Acontreras	Aoverby	Aoverby
82		PU	Del	PU	Del	PU	Del
83		1	5			8
84		2		5			5
85		3			47
86
87
88		Acontreras	Del	47	47
89
Actual Late Loads

Let B88 house: Acontreras and C88 Del.

Using definite ranges, we have:

=SUMPRODUCT(($B$81:$G$81=B88)*($B$82:$G$82=C88)*$B$83:$G$85)

In order to work with dynamic ranges:

( 1.) Activate the target workbook.
( 2.) Activate Insert|Name|Define.
( 3.) Enter BigNum as name in the Names in Workbook box.
( 4.) Enter the following in the Refers to box:

9.99999999999999E+307

( 5.) Click OK.
( 6.) Insert a new worksheet and name it Admin.
( 7.) In A2:A4 in Admin, enter the following labels:

{"# Rows in Actual Late Loads";"# Data Recs in Actual Late Loads";"# Data Columns in Actual Late Loads"}

( 8.) In B2 enter:

=MATCH(BigNum,'Actual Late Loads'!B:B)

( 9.) In B3 enter:

=B2-(CELL("Row",'Actual Late Loads'!B81)-1)

(10.) In B4 enter:

=MATCH(REPT("z",90),'Actual Late Loads'!81:81)-(CELL("Col",'Actual Late Loads'!B81)-1)

81 is the start row and B81 is the start cell.

(11.) Activate Insert|Name|Define.
(12.) Enter DTable as name in the Names in Workbook box.
(13.) Enter the following dynamic formula in the Refers to box:

=OFFSET('Actual Late Loads'!$B$81,0,0,Admin!$B$3,Admin!$B$4)

(13.) Click OK.

Now we can use a different SUMPRODUCT formula:

=SUMPRODUCT((INDEX(DTable,1,0)=B88)*(INDEX(DTable,2,0)=C88)*INDEX(DTable,3,0):INDEX(DTable,Admin!B3,0))