Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
how to create a conditional formula for a range of cells? | Excel Worksheet Functions | |||
select a range using "cells()" | Excel Worksheet Functions | |||
How to create a formula for a range of cells | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions |