Is there an easier way?
"Alexey" wrote...
Hi *Can anyone advise if there is an easier and less complicated way to
achieve the following :
[reformatted]
=SUM(
$C127
+(SUM(
IF(
ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)),
0,
VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)
)-(IF(
ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),
0,
VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)
))
))
)
Eliminate the unnecessary SUM calls. SUM(a+b) can ALWAYS be replaced
by a+b.
Eliminate the unnecessary parentheses.
If there could be multiple instances of the A124 value in MISDATA!
A3:A5000 or MISDATA!D3:D5000, your formula would use only the first
(topmost) one found, and there's no simpler approach than what you're
using. In which case you could reduce your formula to
=$C127
+IF(ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)), 0,
VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0))
-IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),0 ,
VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))
OTOH, if there'd only be at most one instance of the A124 value in
both MISDATA!A3:A5000 or MISDATA!D3:D5000, you could use SUMIF. For
example,
=$C127
+SUMIF(MISDATA!$A$3:$A$5000,$A124,MISDATA!$B$3:$B$ 5000)
-SUMIF(MISDATA!$D$3:$D$5000,$A124,MISDATA!$E$3:$E$5 000)
|