Offset function attempting to sum specific column in the array

[xljgd]

=SUM(OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),976,1,1),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),982,1,2),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),986,1,1))


I attempted to work with offset and match but struggling to automate it a bit.

The above reads from a file fsees a match and if found sums specific columns (976 + 982,983 and 986) to read specific columns from the array.

This works. The problem is i need to repeat this sequence for every 24 cells .


Below i have the same formula but i am manually adding 24 to each cell. How to automate it to fix the column reference for 976,982 and 986 so that i can just use it for my entire data set.

=SUM(OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),976+24,1,1),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),982+24,1,2),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),986+24,1,1))

 

[bosco_yip]

Always upload a file to state what you have? and what do you want? with expected results in few example lines.

Regards

 

[xljgd]

I have data that is forecasted till year 2026. Based on the project match i need to sum the following roles for multi year.

The data is filled for 24 columns and is distributed multi year.

My project data is in another file so i try to match the data and if found selectively sum the following roles. I do get the answer but for the next set i need to add 24 for every year.

SUM(OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),976,1,1),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),982,1,2),OFFSET('2020 Project Load(Budgeted)'!C3,MATCH('Do No Modify! 2020 PL(Summary)'!C12,'2020 Project Load(Budgeted)'!C4:C143,0),986,1,1))


From the project no. as reference i have the Role 2 at 976 column i than sum Role 2, Role 7,8 and 12.

For the next year i add 24 to the next set . For each project i have to add a different set of data like Group 1 and Group 2.

Is there a way were i can reference the column and just add 24 and apply this to the entire data set.

Group 1	Role 2, Role7,Role 8, Role 12
Group 2	Role 10,11, Role 17 thru 24,

 

[xljgd]

I figured out the way to get the no after every 24 column.

i am trying to a vlookup function were sum only if there is a match. can anyone help with the syntax.

i have project nos in another file from A1 thru A50.

A

5​

2​

0​

0​

0​

0​

0​

0​

5​

2​

0​

3​

0​

0​

0​

eg if Project A is found in the file than execute this function.

=SUM(OFFSET('2020 Project Load(Budgeted)'!$AKQ$12,,(COLUMNS($E$12:E12)-1)*24,,1))

 

[bosco_yip]

A

5​

2​

0​

0​

0​

0​

0​

0​

5​

2​

0​

3​

0​

0​

0​

eg if Project A is found in the file than execute this function.

=SUM(OFFSET('2020 Project Load(Budgeted)'!$AKQ$12,,(COLUMNS($E$12:E12)-1)*24,,1))

1] You don't know how to handle the basic Offset function.

2] I remove the Sum function and test the Offset function
=OFFSET('2020 Project Load(Budgeted)'!$AKQ$12,,(COLUMNS($E$12:E12)-1)*24,,1)

And

It always return A,0....... , but I think you wanted return 5,5,.......

3] Without any criteria and expected results in the attachment table, we unable to give any help, please read our forum rule.

Good luck.

 

[xljgd]

I do apologize for not being clear in my explanation.

please find attached the file were i have worked out the problem.

I had used sum since i had to sum 2 or 3 values in the array.

The project name reads from another file. wanted to write the offset in a condition the criteria being
vlookup the project in the other file if found than sum else no records found.

Eg.
if project A is found than executer this formula
=SUM(OFFSET($C$3,,(COLUMNS($B$17:B17)-1)*24,,1)+SUM(OFFSET($J$3,,(COLUMNS($B$17:B17)-1)*24,,2)))

I do appreciate your time . i do hope the attached table is helpful in understanding my problem at hand.

 

[bosco_yip]

Here is my modified table layout


In Cell A16, which have a dropdown list created by Data Validation, for you to select Project A or B or C .......

Then,

1] B17, formula copied across right :

=SUM(N(OFFSET($A$2,MATCH($A$16,$A$3:$A$6,0),{2,9,10}+(COLUMNS($B$1:B$1)-1)*24)))

2] B18, formula copied across right:

=SUM(N(OFFSET($A$2,MATCH($A$16,$A$3:$A$6,0),{11,16,17,18,19,20,21,22}+(COLUMNS($B$1:B$1)-1)*24)))

Remark:
1] since you have requirement for what you wanted, then, can create a constant array e.g.: {2,9,10} and adjusted by: (COLUMNS($B$1:B$1)-1)*24, for every 24 column in afterward.

2] N(OFFSET......{......}), Why add a N function before Offset?
It was because we use a constant array inside a Offset function, N function in this formula purpose is to force Offset something called "Excel dereferencing", Please Google of this name for more details.

Regards

 

[xljgd]

Thank you . That worked like a charm. i also got to learn something about Excel dereferencing. Do appreciate this forum continued support.