Cont'd - Offsetting within a range dynamically - But returning a sum of all instances criteria are m

[S Davis]

This is a continuation of a previous problem. Here was the original
solution to offsetting dynamically within a range whenever criteria are
met:

=((OFFSET(INDEX(Car1,MATCH(1,(Car1=$AU9)*(Repair1=$AV9),0)),0,6)*K9))

Alright. Let's take this a step further.

What the formula above is doing is looking up where in a list two
values are met in the same row, and then offsetting out to return a
third number within that row (and then multiply it by a value).
Basically, its saying 'when there is a certain type of vehicle and a
certain type of repair, display the quantity of parts needed for that
repair' (the QTY being 6 columns out.)

The thing is, there are many times within the reference data that a
certain vehicle and repair are displayed. This is because every part
required, which has a quantity value, says the vehicle and repair type
for that part. Obviously most repairs require more than one part.

So what I need to do is find EVERY instance that vehicle and repair
match up, and then return the sum of all parts (qty) required.
Essentially:

Part1...Car 1...Repair1...Qty
Part2...Car 1...Repair1...Qty
Part3...Car 1...Repair1...Qty
Part4...Car 2...Repair1...Qty
Part5...Car 3...Repair2...Qty


What I want to do is return the total QTY for all Car1 with Repair1.
The result will be the total number of parts required, as this figure
will be multiplied by the forecast amount of workload (ie. the number
of times the repair is expected to happen).


Within the formula below, what's inside the offset brackets returns the

Qty - Qty is 6 columns out within the list I have. K9 is the workload
figure.


Thanks

 

[S Davis]

Hmm. Solution is much easier than anticipated.

{=SUMPRODUCT((Car1=C2)*(Repair1=D80)*QTY}



Sorry to bother everyone