Using the Sumproduct

P93 is blank on both sheets. The errors in the workbook have been pasted back as values; seeing the formula which produced those errors would be helpful.

A clearer visualization of the source data; what logic or calculations should be applied, and what an expected result should be; and it's location; would all be helpful.

Thanks.
Pete

Apologies.
the values error were mainly because I needed to add an IFerror to the formula. I had re-pasted the values.

Yes bebo021999 you're quite correct in the first point..

the formula is in K41 sheet step2.

=K5*('C:\Users\jtahir\Downloads\[workbookname]Weights'!$H$80/SUM('C:\Users\jtahir\Downloads\[QGDP_ChainedLinking_Indicators.xlsx]Weights'!$H$80:$H$82))+K6*('C:\Users\jtahir\Downloads\[workbookname]Weights'!$H$81/SUM('C:\Users\jtahir\Downloads\[QGDP_ChainedLinking_Indicators.xlsx]Weights'!$H$80:$H$82))+K7*('C:\Users\jtahir\Downloads\[workbookname]Weights'!$H$82/SUM('C:\Users\jtahir\Downloads\[workbookname]Weights'!$H$80:$H$82))

I want this to be automated with sumproduct.
Example:
group 42 - the formula should take all the codes starting from 42.. like 4210, 4220 and 4290 and aggregate them using the formula:

[4210+ [4220]+[4290]
[step2k5 * (weightsH80/SUM(weightsH80:H82))] + [step2k6 * (weightsH80/SUM(weightsH80:H82))] + [step2k7 * (weightsH80/SUM(weightsH80:H82))]
[57.5]+[15.6]+[26.25]

now for group 86 I have to make this equation 5 times as there are 5 items in group 82.
I was hoping to loop thru the values so this could be automated.

hope I'm clear.

please see the enclosed updated

so 2018 data will have 2018 base. but 2019 will have 2018(H) as base year. and 2020 will have 2019(I) base year. all are linked

2019 step 2 data in cell p5 will be multiplied by (weightsH80/SUM(weightsH80:H82). 2020 step 2 data in cell u5 will be multiplied by (weights of column I(2019data) in sheet weights). and so on.. 2021 later will have 2020 data when available

thanks a lot bebo021999 !
This actually works but is quite a lot complex.
any chance to make it any easy as I'm a beginner

oh bebo021999!

becuase i edited the data heavily this formula is not working for me in my master file.i tried renaming and changing the cell locations accordingly. but it returns the " " due to error

is it possible for you to tell me what the arguments are doing?

apologies. i got it right! thanks!

but what about the return value.

so if the groups are only having 1 value in step 2 and no matches in the weights sheet. how can I return the same value from step 2. so that no calculation is needed and it returns the same value

step 2 K95 - has gorup 44. this value as appears once in the table and is not found at all in the weights sheet should remain the same as 98.1050357157985 as K13

see the updated workbook

hi bebo021999

Any luck?