dynamically select a range of cells for use in a formula
 

I would like to define a range of cells for a formula based upon the
value of a label cell on the same row.

As an example. I have the following formula:
=COUNTIF('Equip CMG'!$J$4:$J$78,"<"&$A3)
I would like the range J4:J78 to be defined based on a label in column
F. In this example, there are about 100 rows of data. The labels in
column F are always "continuous" grouped together, but their position
in the table and quantity changes. The range is defined as cells
offset first occurrance through offset last occurrance.

Another way to attack this problem is:

Count the number of cells in the range J4:J78 where the value is <A3
AND F3="RoomA". Here we use the whole data range with two criteria.

Any help is greatly appreciated.
V/R,
Carl


--
isofuncurves
------------------------------------------------------------------------
isofuncurves's Profile: http://www.excelforum.com/member.php...o&userid=30749
View this thread: http://www.excelforum.com/showthread...hreadid=507494

dynamically select a range of cells for use in a formula
 

So, does F4:F78 contain product names and J4:J78 contain quantities? You
want to count the number of items in column F that match the product
description in F3 and have a quantity less than the amount in A3?

=SUMPRODUCT(--(J4:J78<A3),--(F4:F78=F3))


"isofuncurves" wrote:


I would like to define a range of cells for a formula based upon the
value of a label cell on the same row.

As an example. I have the following formula:
=COUNTIF('Equip CMG'!$J$4:$J$78,"<"&$A3)
I would like the range J4:J78 to be defined based on a label in column
F. In this example, there are about 100 rows of data. The labels in
column F are always "continuous" grouped together, but their position
in the table and quantity changes. The range is defined as cells
offset first occurrance through offset last occurrance.

Another way to attack this problem is:

Count the number of cells in the range J4:J78 where the value is <A3
AND F3="RoomA". Here we use the whole data range with two criteria.

Any help is greatly appreciated.
V/R,
Carl


--
isofuncurves
------------------------------------------------------------------------
isofuncurves's Profile: http://www.excelforum.com/member.php...o&userid=30749
View this thread: http://www.excelforum.com/showthread...hreadid=507494