# I need an updated excel 2007 formula

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%
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office

Last Comment
nutsch
nutsch

Use MOD

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

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%
nutsch

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

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

And it should be 100%
nutsch

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
wrt1mea

nutsch

Simply amazing. Thanks for the dedication and patience.

Look for more questions from me.
nutsch

Glad to help, thanks for the kind words.

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