Modify the formula to fetch the total Amounts for each name from all Sheets of the file

[Hany ali]

Hello My Master,Based on my professor’s

bosco_yip​

instructions that there be only one request to participate, a new post has been opened for this request ,it is a modification of the equation to bring the total amounts of each name from all Sheets of the file, provided that the amount is divided, in the case of names between the + sign, by the number of names present between this sign.
for C , D and E columns

=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$2:$N$7&"'!$c$2:$c$164"),$B2,INDIRECT("'"&$N$2:$N$7&"'!$b$2:$b$164")))

 

[bosco_yip]

You need to set up a "Resource Allocation Table" from Sheet1 to sheet6

So,

In "Sheet1" the below formulas copied down:

[G3] =IFERROR(@FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("+",,C$3:C$164),"+","</b><b>")&"</b></a>","//b["&ROW(A1)&"]"),"")

In "Sheet1" the below formula copied across right and down:

[H3] =IF($G3="","",FILTERXML("<a><b>"&CONCAT(SUBSTITUTE($C$3:$C$164&"+","+","</b><b>"&INDEX($A$3:$E$164,,MATCH(H$2,$A$2:$E$2,0))&"</b><b>"))&"</b></a>","a/b[.>=0]["&ROW($A1)&"]")/FILTERXML("<a><b>"&CONCAT(SUBSTITUTE($C$3:$C$164&"+","+","</b><b>"&(LEN($C$3:$C$164)-LEN(SUBSTITUTE($C$3:$C$164,"+",""))+1)&"</b><b>"))&"</b></a>","a/b[.>=0]["&ROW($A1)&"]"))

Then,

All the above "Sheet1 formulas" copied to Sheet2 to Sheet6

And

2] In "Total Sheet", the below formula copied down:

[B2] =IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,Sheet1:Sheet6!G$3:G$211)&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

[C2] =IF($B2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$N$2:$N$7&"'!$G$2:$G$211"),$B2,INDIRECT("'"&$N$2:$N$7&"'!$H$2:$H$211"))))

[D2] =IF($B2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$N$2:$N$7&"'!$G$2:$G$211"),$B2,INDIRECT("'"&$N$2:$N$7&"'!$I$2:$I$211"))))

[E2] =IF($B2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$N$2:$N$7&"'!$G$2:$G$211"),$B2,INDIRECT("'"&$N$2:$N$7&"'!$J$2:$J$211"))))

Regards

 

[Hany ali]

Thank you very much. You are truly a genius and I sincerely apologize for your trouble with me

I believe that this is the only solution available.. Is there no solution with a direct equation for my request?
But will this affect the handling of the file or make it large?
this is so that I can deal with the file easily. My original file has different Sheet names

 

[Hany ali]

Is there no solution with a direct equation for my request?

 

[Hany ali]

UP Please

 

[Hany ali]

Hello ...Is there no solution with a direct equation for my request?

 

[Hany ali]

UP Please