=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))
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))