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

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 2, 2012
Messages
1,991
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you try the formula in post 2?
 

mmr1

Board Regular
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
I tried in J6 it is return #VALUE!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
I thought it might be something like that. This gives the correct results based on the example provided.
Book1
HIJK
5Ref CodeTradesTotal HoursTotal Amount
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
Joined
Aug 25, 2020
Messages
63
Office Version
  1. 365
Platform
  1. Windows
many thanks Jason, for your formula solution.

solved the is issue as expected.
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top