I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)=200, <=1000) But, I need to know what it actually should be!

Also, I need to know how to keep one cell a constant in a function that i am
repeating. For example, the function that I want in C5 is "=B5/I4". Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in every
equation, but B will change as I go down the spreadsheet. How can I get this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!

Hi,

To count the numbers in a range

=SUMPRODUCT((C1:C100=200)*(C1:C100<=1000))

and your second question. Put this in C6 and drag down
=B6/$I$4

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Hoov" wrote:

I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)=200, <=1000) But, I need to know what it actually should be!

Also, I need to know how to keep one cell a constant in a function that i am
repeating. For example, the function that I want in C5 is "=B5/I4". Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in every
equation, but B will change as I go down the spreadsheet. How can I get this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!

Hi Hoov
Try this =COUNTIF(C1:C1:C100,"=200")-COUNTIF(C1:C100,"1000")
Second question : =B5/$I$4
Use the dollar sign to make it absolute.
HTH
John
"Hoov" wrote in message
...
I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)=200, <=1000) But, I need to know what it actually should be!

Also, I need to know how to keep one cell a constant in a function that i am
repeating. For example, the function that I want in C5 is "=B5/I4". Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in every
equation, but B will change as I go down the spreadsheet. How can I get this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!

Q1:
Some examples of formulas (not functions - they are something else) you
could use
(a) =COUNTIF(C1:C100,"=200") - COUNTIF(C1:C100, "<=1000)
(b) =SUMPRODUCT(--(C1:C100=200),--(C1:C100<=1000))
(c) =COUNTIFS(C1:C100,"=200",C1:C100,"<=1000") ' only in Excel 2007

COUNTIF is explained in Help
SUMPRODUCT is explained in these:
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct

2) =B5/$I$4
You can type the $ sings to make the refercne 'absolute' of with the cursor
on eiter the I or the 4 press F4
Chip explains this topic at
http://www.cpearson.com/excel/relative.aspx

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Hoov" wrote in message
...
I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the
range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)=200, <=1000) But, I need to know what it actually should
be!

Also, I need to know how to keep one cell a constant in a function that i
am
repeating. For example, the function that I want in C5 is "=B5/I4".
Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in
every
equation, but B will change as I go down the spreadsheet. How can I get
this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!

Thank you so much Mike, this worked perfectly!

"Mike H" wrote:

Hi,

To count the numbers in a range

=SUMPRODUCT((C1:C100=200)*(C1:C100<=1000))

and your second question. Put this in C6 and drag down
=B6/$I$4

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Hoov" wrote:

I have two different questions.

First, I need to know what function to enter to determine how many
quantities of a particular item exist on a spreadsheet. For example, if I
wanted to know how many number existed in C1-C100 that were within the range
of $$200-$1000, how would I enter that function. Logically, it might be

=QTY((C1:C100)=200, <=1000) But, I need to know what it actually should be!

Also, I need to know how to keep one cell a constant in a function that i am
repeating. For example, the function that I want in C5 is "=B5/I4". Well,
in C6 i want B6/I4, and in C7 i need it to be B7/I4. So, I4 will be in every
equation, but B will change as I go down the spreadsheet. How can I get this
to automatically repeat over 200 cells or so?

Thanks so much, I greatly appreciate it!