Re: SUMPRODUCT formula messed up

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Jul 12, 4:14 pm, "Peo Sjoblom" <terr...@xxxxxxxx> wrote:
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" <lan.be...@xxxxxxxxxxxx> wrote in message

news:1184267133.663263.132880@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On Jul 12, 2:54 pm, "Peo Sjoblom" <terr...@xxxxxxxx> 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" <lan.be...@xxxxxxxxxxxx> wrote in message

news:1184266042.694757.97080@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Jul 2, 2:47 pm, "Peo Sjoblom" <terr...@xxxxxxxx> 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" <lan.be...@xxxxxxxxxxxx> wrote in message

news:1183401111.029928.174310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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 spread***, 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.- Hide quoted text -

- Show quoted text -

Peo, but hang on. When i go back to my report work***, it is a
different case. In my report work***, 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

.

Quantcast