Calculation formula needed

[Guest]

Hello, I have Excel 97, and am trying to learn formulas. I am trying to find out what formula to use in order to calculate the following
I have this in my worksheet
A B C D
1 50 25 TRUE 5
2 20 30 FALSE 3
3 80 60 TRUE -2
4 10 40 FALSE -1
5 35 25 TRUE
6 40 30 TRUE -1
7 10 15 FALSE

Formulas (filled down) are used above: Cell C1 =A1>B1 Cell D1 =SUM(B2-B1
What I want: In Cell G100 I want the calculated result in the form of a number that FIRST finds the "TRUE" ocurrances in column "C", then add together all the 'positive' values in column "D" that are next to "TRUE" found in column "C". The above example would yield a G100 value of 10
Then in Cell G101 I want I want the calculated result in the form of a number that FIRST finds the "TRUE" ocurrances in column "C", then add together all the 'negative' values in column "D" that are next to "TRUE" found in column "C". The above example would yield a G101 value of -35
What would the Cell G100 & G101 formula's look like? I hope to achieve this calculation without manually using "autofilter". Thanks to anyone who can help
Sam

 

[Frank Kabel]

Hi
try
G100:
=SUMPRODUCT(--(C1:C100),--(D1100>0),D1100)

and G101:
=SUMPRODUCT(--(C1:C100),--(D1100<0),D1100)

You could even skip column C using
=SUMPRODUCT(--(A1:A100>B1:B100),--(D1100>0),D1100)
and
=SUMPRODUCT(--(A1:A100>B1:B100),--(D1100<0),D1100)

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(C1:C7=TRUE),--(D17>0),D17)

and for negative TRUE

=SUMPRODUCT(--(C1:C7=TRUE),--(D17<0),D17)

or you could skip column C
and do it in one fell swoop

=SUMPRODUCT(--(A1:A7>B1:B7),--(D17>0),D17)

and

=SUMPRODUCT(--(A1:A7>B1:B7),--(D17<0),D17)

--

Regards,

Peo Sjoblom

Hello, I have Excel 97, and am trying to learn formulas. I am trying to

find out what formula to use in order to calculate the following.

I have this in my worksheet:
A B C D
1 50 25 TRUE 5
2 20 30 FALSE 30
3 80 60 TRUE -20
4 10 40 FALSE -15
5 35 25 TRUE 5
6 40 30 TRUE -15
7 10 15 FALSE

Formulas (filled down) are used above: Cell C1 =A1>B1 Cell D1 =SUM(B2-B1)
What I want: In Cell G100 I want the calculated result in the form of a

number that FIRST finds the "TRUE" ocurrances in column "C", then add
together all the 'positive' values in column "D" that are next to "TRUE"
found in column "C". The above example would yield a G100 value of 10.

Then in Cell G101 I want I want the calculated result in the form of a

number that FIRST finds the "TRUE" ocurrances in column "C", then add
together all the 'negative' values in column "D" that are next to "TRUE"
found in column "C". The above example would yield a G101 value of -35.

What would the Cell G100 & G101 formula's look like? I hope to achieve

this calculation without manually using "autofilter". Thanks to anyone who
can help!