"TRUE" "TRUE" formula needed

[Guest]

Hello, I have Excel 97. I have this in my worksheet:

new column
A B C D E
1 50 25 TRUE 5 TRUE
2 20 30 FALSE 30 FALSE
3 80 60 TRUE -20 TRUE
4 10 40 FALSE -15 TRUE
5 35 25 TRUE 5 TRUE
6 40 30 TRUE -15 FALSE
7 10 15 FALSE

I was previously sent the following formula which worked great for finding every "TRUE" in column C and calculating the values in column D next to every "TRUE" in column C.
=SUMPRODUCT(--(C1:C7=TRUE),--(D17>0),D17) for positive TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(D17<0),D17) for negative TRUE

Now I need to do the same as above but add column E into the mix. What do I add to the above formulas? I want to end up with only all TRUE's in columns C & E. One formula for positive values and one for negative as above.
Thanks again for your time!
Sam

 

[Frank Kabel]

Hi Same
shouldn't be that complicated
try
=SUMPRODUCT(--(C1:C7=TRUE),--(D17>0),--(E1:E100=TRUE),D17)
and
=SUMPRODUCT(--(C1:C7=TRUE),--(D17<0),--(E1:E100=TRUE),D17)

 

[Leo Heuser]

Hello Sam

One way:

=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D17>0),D17) for positive
TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D17<0),D17) for negative
TRUE

another way

=SUMPRODUCT((C1:C7=TRUE)*(E1:E7=TRUE)*(D17>0)*D17) for positive TRUE
=SUMPRODUCT((C1:C7=TRUE)*(E1:E7=TRUE)*(D17<0)*D17) for negative TRUE

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hello, I have Excel 97. I have this in my worksheet:

new column
A B C D E
1 50 25 TRUE 5 TRUE
2 20 30 FALSE 30 FALSE
3 80 60 TRUE -20 TRUE
4 10 40 FALSE -15 TRUE
5 35 25 TRUE 5 TRUE
6 40 30 TRUE -15 FALSE
7 10 15 FALSE

I was previously sent the following formula which worked great for finding

every "TRUE" in column C and calculating the values in column D next to
every "TRUE" in column C.

=SUMPRODUCT(--(C1:C7=TRUE),--(D17>0),D17) for positive TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(D17<0),D17) for negative TRUE

Now I need to do the same as above but add column E into the mix. What do

I add to the above formulas? I want to end up with only all TRUE's in
columns C & E. One formula for positive values and one for negative as
above.

 

[Aladin Akyurek]

Hello Sam

One way:

=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D17>0),D17) for positive
TRUE
=SUMPRODUCT(--(C1:C7=TRUE),--(E1:E7=TRUE),--(D17<0),D17) for negative
TRUE

[...]

Leo,

Why not dispense with the equality test...

=SUMPRODUCT(--C1:C7,--E1:E7,--(D17>0),D17)
=SUMPRODUCT(--C1:C7,--E1:E7,--(D17<0),D17)

Aladin