Formula to calculate

D

Derrick

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
 
T

Thomas Ramel

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
Click to expand...

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]
 
F

Frank Kabel

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
 
Top