Thread: How can i shorten my formula

Hi,

I am summing using "isnumber" and "vlookup" but the formula is too long; can some teach me how to shorten this formula?

Thanks in advance for your help.

=IF(ISNUMBER(VLOOKUP(E34,'429'!E:K,7,FALSE)),(VLOOKUP(E34,'429'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP (E34,'570'!E:K,7,FALSE)),(VLOOKUP(E34,'570'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,'574'!E:K,7,FAL SE)),(VLOOKUP(E34,'574'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,'575'!E:K,7,FALSE)),(VLOOKUP(E34,'5 75'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,'576'!E:K,7,FALSE)),(VLOOKUP(E34,'576'!E:K,7,FALSE)),0) +IF(ISNUMBER(VLOOKUP(E34,'577'!E:K,7,FALSE)),(VLOOKUP(E34,'577'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP (E34,'578'!E:K,7,FALSE)),(VLOOKUP(E34,'578'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,'579'!E:K,7,FAL SE)),(VLOOKUP(E34,'579'!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,M2MKT2!E:K,7,FALSE)),(VLOOKUP(E34,M 2MKT2!E:K,7,FALSE)),0)+IF(ISNUMBER(VLOOKUP(E34,'580'!E:K,7,FALSE)),(VLOOKUP(E34,'580'!E:K,7,FALSE)), 0)


Thanks,
Naiomi

First, enter your sheet names in a range of cells, for example A2:A10. Then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$10&"'!E:E"),E34,INDIRECT("'"&$A$2:$A$10&"'!K:K")))

Hope this helps!