SUMPRODUCT formula messed up
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
"Positive" wrote in message
ups.com...
On Jul 12, 2:54 pm, "Peo Sjoblom" wrote:
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
'
"Positive" wrote in message
ps.com...
On Jul 2, 2:47 pm, "Peo Sjoblom" wrote:
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
"Positive" wrote in message
groups.com...
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 otherformulase.g IF, VLOOK never been messed up when I work on
the file or save it to a different file.
Is there anyway I couldprotectthis SUMPRODUCT formula or any reason
why it get messed up so easily?
Thanks
Positive- Hide quoted text -
- Show quoted text -
For example, I have a spreadsheet, when i insert a row, then all my
SUMPRUCTformulasget messed up- Hide quoted text -
- Show quoted text -
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.
|