Hi, I can't figure out a single formula that will do the following
In column C locate only values between -1 through -50, then in column A locate only the TRUE's, then in column B calculate the total $ amount.
The formula would result an answer for this example of $35. Rows 1 & 5 supply the answer.
A B C
1 TRUE $20 -1
2 FALSE $10 -2
3 TRUE $30 3
4 TRUE $50 -9
5 TRUE $15 -4
6 FALSE $25 -22

What would the formula look like? I use excel 97. I do not want to use the autofilter option, would rather program a single cell for answer
Thank you very much for your help
Derric

Grüezi Derrick

Derrick schrieb am 16.05.2004

Hi, I can't figure out a single formula that will do the following: In
column C locate only values between -1 through -50, then in column A
locate only the TRUE's, then in column B calculate the total $ amount.
The formula would result an answer for this example of $35. Rows 1 & 5
supply the answer.
A B C
1 TRUE $20 -19
2 FALSE $10 -20
3 TRUE $30 30
4 TRUE $50 -90
5 TRUE $15 -43
6 FALSE $25 -22

Try the following formula:

=SUMPRODUCT((A1:A6="TRUE")*(C1:C6-51)*(C1:C6<0)*B1:B6)

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win 2000Pro SP-4 / xl2000 SP-3]

Hi
or as an alternative to Thomas' suggestion:
=SUMPRODUCT(--(A1:A6),--(C1:C6=-50),--(C1:C6<=-1),B1:B6)

Assumption: A1:A6 contains real boolean values
--
Regards
Frank Kabel
Frankfurt, Germany


Derrick wrote:
Hi, I can't figure out a single formula that will do the following:
In column C locate only values between -1 through -50, then in column
A locate only the TRUE's, then in column B calculate the total $
amount. The formula would result an answer for this example of
$35. Rows 1 & 5 supply the answer. A B C 1
TRUE $20 -19 2 FALSE $10 -20
3 TRUE $30 30
4 TRUE $50 -90
5 TRUE $15 -43
6 FALSE $25 -22

What would the formula look like? I use excel 97. I do not want to
use the autofilter option, would rather program a single cell for
answer. Thank you very much for your help!
Derrick