# correct the formula to return a multiplying values duration with planned multiple matches.

#### mmr1

##### Board Regular
Hi,

Required a help to correct the formula which is work in first cell perfectly and given a seeking results then for getting further results when I can drag to below cell it returns #REF!.

=SUMPRODUCT(\$B\$7:\$B\$23,INDEX(\$C\$7:\$F\$23,,MATCH(H6,\$C\$4:\$F\$4,0),MATCH(I6,\$C\$5:\$F\$5,0)))

Thanks for the help,

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### jasonb75

##### Well-known Member
The formula makes no sense without a sample of the sheet and an explanation of what you want to do.
You are matching to one of 4 areas but there is only one in your index range so the formula will only work if I6 is an exact match for C5.

I have my suspicions that you actually need
Excel Formula:
``=SUMPRODUCT(\$B\$7:\$B\$23*\$C\$7:\$F\$23*(\$C\$4:\$F\$4,=H6)*(\$C\$5:\$F\$5=I6))``
but that is only a guess given the lack of information.

#### pjmorris

##### Well-known Member
I agree with Jason's comment that understanding the formula is difficult without at least example data to work with - use XL2BB (see link in Jason's signature block).

The advice I would give is to use the 'Evaluate Formula' tool on the Formula Auditing tab of the Formula Ribbon. It will help you identify exactly what is returning the #Ref error.

HTH

#### mmr1

##### Board Regular
Hi,

Thank you all of you for your responses herewith attached a sample sheet for your reference and for expected results.

Book2.13.12.2020 (3).xlsx
BCDEFGHIJK
2
3Work plannedProduction / unit Hours
4Ref Code1000101010201025
5Duration (Days)Skilled labourSemi Skilled labourHelperBanksman / RiggerRef CodeTradesTotal HoursTotal Amount
6Production Rate Per Hour654.56.51000Skilled labour26948161,687.45
71240301010Semi Skilled labour#REF!#REF!
8124040301020Helper
91025Banksman / Rigger
10268010Total Hrs/Amount#REF!#REF!
11414040
12
137250Expected Results
1426948161,687.45
15Sub Total-1200409070487224,361.00
1679.5560524023,580.00
17398325,887.28
1875.346010
1987.84505010
2058.285010
21
2223.5040
2378.806010
24
25Sub Total-232050040
Sheet1 (2)
Cell Formulas
RangeFormula
J6:J7J6=SUMPRODUCT(\$B\$7:\$B\$23,INDEX(\$C\$7:\$F\$23,,MATCH(\$H6,\$C\$4:\$F\$4,0),MATCH(\$I6,\$C\$5:\$F\$5,0)))
K6:K7K6=SUMPRODUCT(\$B\$7:\$B\$23,INDEX(\$C\$7:\$F\$23,,MATCH(\$H6,\$C\$4:\$F\$4,0),MATCH(\$I6,\$C\$5:\$F\$5,0)))*LOOKUP(2,1/(\$C\$4:\$F\$4=\$H6)*(\$C\$5:\$F\$5=\$I6),(C6:F6))
J10:K10J10=SUM(J6:J9)
C15:F15C15=SUM(C7:C14)
C25:F25C25=SUM(C16:C24)

#### jasonb75

##### Well-known Member

Did you try the formula in post 2?

#### mmr1

##### Board Regular
I tried in J6 it is return #VALUE!

#### jasonb75

##### Well-known Member

I missed the text in B15 which is the cause of the error.

Any reason why you're looking at both ref code and trade? Unless you can have 2 different trades with the same code or 2 different codes with the same trade, you only need to check one of them which will make it much easier.

#### mmr1

##### Board Regular
Reason behind is to know actual hours of each trade as related to ref code, mentioned trade is not always belongs to one ref code, it is repeating to all ref codes.

For Example:-Ref Code-1000 have a Trade Skilled Labor, its also be repeats in any time as per work requirement in other codes like 1010,1020,1025. That is why looking both ref code and trade.

Thanks,

#### jasonb75

##### Well-known Member
I thought it might be something like that. This gives the correct results based on the example provided.
Book1
HIJK
61000Skilled labour26947.908161687.448
71010Semi Skilled labour4872.224361
81020Helper524023580
91025Banksman / Rigger3982.65825887.277
10Total Hrs/Amount41042.766235515.725
Sheet2
Cell Formulas
RangeFormula
J6:J9J6=SUMPRODUCT(IF(ISNUMBER(\$B\$7:\$B\$23),\$B\$7:\$B\$23)*\$C\$7:\$F\$23*(\$C\$4:\$F\$4=H6)*(\$C\$5:\$F\$5=I6))
K6:K9K6=J6*LOOKUP(2,1/(\$C\$4:\$F\$4=\$H6)*(\$C\$5:\$F\$5=\$I6),(\$C\$6:\$F\$6))
J10:K10J10=SUM(J6:J9)

#### mmr1

##### Board Regular
many thanks Jason, for your formula solution.

solved the is issue as expected.

Replies
7
Views
255
Replies
19
Views
291
Replies
6
Views
115
Replies
0
Views
207
Replies
4
Views
154

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,825
Messages
5,766,666
Members
425,367
Latest member
Boboka

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back