wrt1mea

asked on

I am using the following formula:

=SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0)))

It works just like I need it to. However, the problem I am having on the production sheet is that since it references an accounting code and returns the percentage, there are some situations where that accounting code is used more than once and it returns a percentage value for the sum of the percentages. What would be a simple way to add to the formula IF the value returned is greater than 100% (or 1), it gets subtracted out?

For example: the above formula returns 188%. It should only be 88%

The above formula returns 200%. It should only be 100%

=SUM(IFERROR(INDEX('[Repor

It works just like I need it to. However, the problem I am having on the production sheet is that since it references an accounting code and returns the percentage, there are some situations where that accounting code is used more than once and it returns a percentage value for the sum of the percentages. What would be a simple way to add to the formula IF the value returned is greater than 100% (or 1), it gets subtracted out?

For example: the above formula returns 188%. It should only be 88%

The above formula returns 200%. It should only be 100%

Last Comment

ASKER

MOD doesnt work right becuase it will subtract 100% from 100% leaving me zero. I need it to only subtract if the number is greater than 100%

then, extending it

=if(SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0)))=1,1,mod(SUM(IFERROR(INDEX('[Report1.xls]Sheet1!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet1!$P$14:$P$1000,0)),0),(IFERROR(INDEX('[Report1.xls]Sheet2!$AX$14:$AX$1000,MATCH(B8,'[Report1.xls]Sheet2'!$P$14:$P$1000,0)),0))),1))

=if(SUM(IFERROR(INDEX('[Re

ASKER

OK, That works for anything over 100%, but for the few items that I have that are 200%, its returning a zero.

ASKER

And it should be 100%

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

nutsch

Simply amazing. Thanks for the dedication and patience.

Look for more questions from me.

Simply amazing. Thanks for the dedication and patience.

Look for more questions from me.

Glad to help, thanks for the kind words.

Thomas

Thomas

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY

=MOD(SUM(IFERROR(INDEX('[R