Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cont'd - Offsetting within a range dynamically - But returning a sum of all instances criteria are met
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 wrote: =((OFFSET(INDEX(Car1,MATCH(1,(Car1=$AU9)*(Repair1= $AV9),0)),0,6)*K9)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cont'd - Offsetting within a range dynamically - But returning a sum of all instances criteria are met
Hmm. Solution is much easier than anticipated.
{=SUMPRODUCT((Car1=C2)*(Repair1=D80)*QTY} :( Sorry to bother everyone:) S Davis wrote: 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 wrote: =((OFFSET(INDEX(Car1,MATCH(1,(Car1=$AU9)*(Repair1= $AV9),0)),0,6)*K9)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a range after multiple criteria | Excel Discussion (Misc queries) | |||
Dynamically Generated Range using Macro | Excel Worksheet Functions | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
How can I dynamically eliminate blank cells in a given range in E. | Excel Worksheet Functions | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions |