SUMPRODUCT formula messed up

P

Positive

When I use SUMPRODUCT formula, especially to look up data from
different workbooks and return to a different workbook, I tend to
have
problem later on when I work on it or save it to a different file.
All
my other formulas e.g IF, VLOOK never been messed up when I work on
the file or save it to a different file.

Is there anyway I could protect this SUMPRODUCT formula or any reason
why it get messed up so easily?
Thanks


Positive
 
P

Peo Sjoblom

SUMPRODUCT is no different than any other functions in this regard,
you might want to give us an example where VLOOKUP using same references as
SUMPRODUCT acted any differently
 
P

Positive

SUMPRODUCT is no different than any other functions in this regard,
you might want to give us an example where VLOOKUP using same references as
SUMPRODUCT acted any differently

--
Regards,

Peo Sjoblom








- Show quoted text -
Click to expand...

For example, I have a spreadsheet, when i insert a row, then all my
SUMPRUCT formulas get messed up
 
P

Peo Sjoblom

That is no different from any other formulas except if you use INDIRECT in a
special way
VLOOKUP behaves the same as SUMPRODUCT in this regard
 
P

Positive

That is no different from any otherformulasexcept if you use INDIRECT in a
special way
VLOOKUP behaves the same as SUMPRODUCT in this regard

--
Regards,

Peo Sjoblom
'







- Show quoted text -
Click to expand...

Peo,
Ok. How about when I insert a row, then all my VLOOKUP and SUMPRODUCT
formulas get messed up? Is there a way to protect my formulas from
being messed up when I edit or add, delete rows? Some messages in the
GROUPs recommend MACRO but I have never use MACRO and don't really
know how to start. Thanks a lot for your prompt response.
 
P

Peo Sjoblom

If you always want to use the same range in your formulas so if you have

=SUMPRODUCT(--($A$2:$A$100=1))

and if you insert a row at the top you don't want it to change to
$A$3:$A$101?


=SUMPRODUCT(--(INDIRECT("$A$2:$A$100")=1))


now there is a drawback to this since INDIRECT is a volatile function
 
P

Positive

If you always want to use the same range in your formulas so if you have

=SUMPRODUCT(--($A$2:$A$100=1))

and if you insert a row at the top you don't want it to change to
$A$3:$A$101?

=SUMPRODUCT(--(INDIRECT("$A$2:$A$100")=1))

now there is a drawback to this since INDIRECT is a volatile function

--
Regards,

Peo Sjoblom







- Show quoted text -
Click to expand...

Peo, but hang on. When i go back to my report worksheet, it is a
different case. In my report worksheet, I have SUMPRODUCT formula that
gets data from different workbook. So the range is applied for a
DIFFERENT WORKBOOK, which means it does not matter how many rows I add
in my report ?

Ex
=SUMPRODUCT(--('M:\243\CLARs\CDC criticizd detail 2007\
[Port.xls]Sheet1'!$A$2:$A$271=20143385),--('M:\243\CLARs\CDC criticizd
detail 2007\[Port.xls]Sheet1'!$B$2:$B$271=42),'M:\243\CLARs\CDC
criticizd detail 2007\[Port.xls]Sheet1'!$V$2:$V$271)*1000+SUMPRODUCT(--
('M:\243\CLARs\CDC criticizd detail 2007\[Port.xls]Sheet1'!$A$2:$A
$271=20143385),--('M:\243\CLARs\CDC criticizd detail 2007\
[Port.xls]Sheet1'!$B$2:$B$271=34),'M:\243\CLARs\CDC criticizd detail
2007\[Port.xls]Sheet1'!$V$2:$V$271)*1000

Thanks
 
Top