Shorten Formulas
 

I read a previous entry where you explained how to shorten a formula...it
didn't really pertain to my needs. Can someone help? I have many g/l
accounts that I need to include in my formula. How can I shorten it. This is
an example of a formula that I have and it is one of the shortest ones I have.
...

=SUM(IF(VALUE(Sheet1!$A$2:$A$5481)=1,IF(VALUE(She et1!$B$2:$B$5481)=340010,IF(VALUE(Sheet1!$B$2:$B$ 5481)<=340060,Sheet1!$K$2:$K$5481,0),0),0)*-1,IF(VALUE(Sheet1!$A$2:$A$5481)=1,IF(VALUE(Sheet1 !$B$2:$B$5481)=341010,IF(VALUE(Sheet1!$B$2:$B$548 1)<=341060,Sheet1!$K$2:$K$5481,0),0),0)*-1)
--
Michelle

One way:

=SUMPRODUCT(--(A2:A5481=1),((B2:B5481=340010)*(B2:B5481<=34006 0) +
(B2:B5481=341010)*(B2:B5481<=341060)),-K2:K5481)

In article ,
MichelleExcelBeginner wrote:

I read a previous entry where you explained how to shorten a formula...it
didn't really pertain to my needs. Can someone help? I have many g/l
accounts that I need to include in my formula. How can I shorten it. This is
an example of a formula that I have and it is one of the shortest ones I
have.
..

=SUM(IF(VALUE(Sheet1!$A$2:$A$5481)=1,IF(VALUE(She et1!$B$2:$B$5481)=340010,IF
(VALUE(Sheet1!$B$2:$B$5481)<=340060,Sheet1!$K$2:$K $5481,0),0),0)*-1,IF(VALUE(S
heet1!$A$2:$A$5481)=1,IF(VALUE(Sheet1!$B$2:$B$548 1)=341010,IF(VALUE(Sheet1!$
B$2:$B$5481)<=341060,Sheet1!$K$2:$K$5481,0),0),0)*-1)

Thank you, I just gave it a try and it gives me the same end value...

What exactly is SUMPRODUCT? I haven't seen that before.

--
Michelle


"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A2:A5481=1),((B2:B5481=340010)*(B2:B5481<=34006 0) +
(B2:B5481=341010)*(B2:B5481<=341060)),-K2:K5481)

In article ,
MichelleExcelBeginner wrote:

I read a previous entry where you explained how to shorten a formula...it
didn't really pertain to my needs. Can someone help? I have many g/l
accounts that I need to include in my formula. How can I shorten it. This is
an example of a formula that I have and it is one of the shortest ones I
have.
..

=SUM(IF(VALUE(Sheet1!$A$2:$A$5481)=1,IF(VALUE(She et1!$B$2:$B$5481)=340010,IF
(VALUE(Sheet1!$B$2:$B$5481)<=340060,Sheet1!$K$2:$K $5481,0),0),0)*-1,IF(VALUE(S
heet1!$A$2:$A$5481)=1,IF(VALUE(Sheet1!$B$2:$B$548 1)=341010,IF(VALUE(Sheet1!$
B$2:$B$5481)<=341060,Sheet1!$K$2:$K$5481,0),0),0)*-1)

Take a look at Help.

For a little more, see

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
MichelleExcelBeginner wrote:

What exactly is SUMPRODUCT? I haven't seen that before.

Thank you again. This was very informative.

Michelle
--
Michelle


"JE McGimpsey" wrote:

Take a look at Help.

For a little more, see

http://www.mcgimpsey.com/excel/doubleneg.html

In article ,
MichelleExcelBeginner wrote:

What exactly is SUMPRODUCT? I haven't seen that before.