• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Hany ali

Active Member
Hello My Master,Based on my professor’s


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


  • unique2.xlsb
    59.7 KB · Views: 4
You need to set up a "Resource Allocation Table" from Sheet1 to sheet6


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)&"]"))


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


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




  • A unique2.xlsb
    212.9 KB · Views: 8
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
Last edited: