Suzanne,

A formula or function can only change the cell it is in or is called from so
you can't do that with either method. You could resort to VB but that would
be overkill for counting. Try this. Create your grouping table on sheet 2 and
then use this formula

=COUNTIF(Sheet1!$A$1:$A$100,"="&B1)-COUNTIF(Sheet1!$A$1:$A$100,"="&C1)

Drag down as required

Col b Col C

0 4999
5000 9999
10000 14999



"Suzanne" wrote:

Thanks, i'm trying to solve with a single function or formula; my values
range from 0 to $80,000. I do appreciate your reply.

--
Thank you -- Suzanne.


"Mike H" wrote:

Suzanne,

I'm not sure I fully understand the question but maybe this which will count
all $ amounts on sheet 1 in the range 0 to 5000

=COUNTIF(Sheet1!A1:A100,"0")-COUNTIF(Sheet1!A1:A100,"5000")


and for the next grouping
=COUNTIF(Sheet1!A1:A100,"=5000")-COUNTIF(Sheet1!A1:A100,"10000")


Mike

"Suzanne" wrote:

I have 14,000 rows of dollar amounts, in Sheet 1 column A, in Sheet 2 i have
groupings set up in $5,000 increments (similarly to frequency array set up).
Grouping column
4999
9999
14999
In Sheet 1 Colum B, i'd like to put a formula that will compare dollar
amount to grouping, fill grouping label in column B. I could set up Sheet 2
with 2 columns;
0 4999
5000 9999
10000 14999
to try and use some 'between' formula which i've yet to find.
End result
$6,869 9999

I must admit, this one has me stumped. Hope someone can assist. Thanks!