Nested IfError(Sumifs) with another iferror(sumifs)

[atowriss]

I am trying to distribute total commission costs over total contracts for a customer. In some instances the Product is in column H of a worksheet and some in Column L of the same worksheet. I want to look in column H 1st, then L and if neither, then zero. I can get my formula to work with just one of the IfError(SumIfs), but not when I add the 2nd. What am I doing wrong? Is there a better way? I have attached a screenshot of my formula. Again, it works If I only use 1 set of the IfError(SumIFS), but when I try to combine the 2, it fails.



Thanks

 

[JMKnapp]

Would you mind copying and pasting your formula as-is so that I can paste it into a test sheet and give it some play?

 

[atowriss]

Would you mind copying and pasting your formula as-is so that I can paste it into a test sheet and give it some play?

Here ya go: =IFERROR(((SUMIFS('Regional Comm'!J:J,'Regional Comm'!A:A,Master!F2047,'Regional Comm'!E:E,Master!B2047,'Regional Comm'!H:H,Master!I2047)/(SUMIFS(A:A,Master!F:F,Master!F2047,Master!B:B,Master!B2047,Master!I:I,Master!I2047)))*A2047),IFERROR((SUMIFS('Regional Comm'!J:J,'Regional Comm'!A:A,Master!F2047,'Regional Comm'!E:E,Master!B2047,'Regional Comm'!L:L,Master!I2047)/(SUMIFS(A:A,Master!F:F,Master!F2047,Master!B:B,Master!B2047,Master!I:I,Master!I2047)))*A2047,0))

Thanks!!

 

[JMKnapp]

I removed file and sheet reference names to make it more simple to read / proof, but didn't change any of the functions. And ... it ran okay. I don't have your sample data, so I can't say that the results would be right ... but the formula ran without any problem. Any chance there's a glitch in some of the external references?

 

[atowriss]

I removed file and sheet reference names to make it more simple to read / proof, but didn't change any of the functions. And ... it ran okay. I don't have your sample data, so I can't say that the results would be right ... but the formula ran without any problem. Any chance there's a glitch in some of the external references?

All my references are in the same workbook. Attached is a sample file that has the two sheets involved:


It shows how when I just have 1 iferror, it works, but when I combined 2, it doesn't