Greater than X, but less than X formulas??

[JENNYC]

Hello...this is hopefully something very easy for one of you experts to answer for me:

I am in need of 2 different formulas.

1. Counting the numbers in Column A that are greater than 60, but less than 90.

2. Give me the sum of the amounts in column B, when it sees a number in column A that is greater than 60, but less than 90.

Please help if you can!! greatly appreciated!

[Ron Coderre]

For amounts listed in cells A1:A11

The count is:
=SUM(($A$1:$A$11<90)*($A$1:$A$11>60))

The sum is:
=SUM(($A$1:$A$11<90)*($A$1:$A$11>60)*A1:A11)


(Note: Commit those array formulas by pressing [Ctrl]+[Shift]+[Enter])

Hope that helps.

Ron

[JENNYC]

Well, one of the formulas Ron recommended below worked perfectly, but i am still in need of a formula to give me the sum of the amounts in column B. Here's a better explanation:

Column A lists the number days something has been in our inventory.
Column B lists the amounts of these items

i need to know the sum (from B) of anything in our inventory that is between 60 and 90 days old (from column A).

Anybody have any advice for me??
(...and thank you Ron!!)

[mav384]

The answer is basically the same as the above formula.

If you look at the answer. The first part in the sum ($A$1:$A$11<90) checks to see if it is less than 90. It returns 1 if it is true. The second part works the same way. If they are both true, you get 1*1*(what you want to add if the conditions are true). In the first case, you wanted to add col A. This time you want to add col B.

=SUM(($A$1:$A$11<90)*($A$1:$A$11>60)*B1:B11)

I hope that helps.

[JENNYC]

Uh oh!! I still can't get it to work for me?? I keep getting that dreaded #VALUE!
I'm so sorry! Any other ideas or any idea of what i might be doing wrong? (i did commit it as an array formula!)

....and thank you very much!!

[Ron Coderre]

You can't have any text in the values to be summed. Is that the problem?

Ron

[JENNYC]

Nope! No text at all?? this is exactly the formula I have:

{=SUM(($J$8:$J$200<90)*($J$8:$J$200>60)*E8:E200)}



thanks!