Defining a Range of Data with a Query

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I have a range of data - say A1: D500. The range is being used like
database with 6 column fields. I want to be able to identify the tw
rows within the range. My data is hourly. The data displays multipl
machine stats over various hours over various dates. I want t
identify the first and last row where the fields match my query.

Say Column A = Date, Column B = Hour, Column C = Machine and Column D
Measured Data .

Lets say I am querying on Date = 01/01/2004, Hour = 1. I need t
search the array to find the start row and end row that have thes
values in them. What I am really trying to do is define the range wit
these two values.

Does anyone know how to do this?

Thank-yo
 
Hi
try something like the following:
1. Get the start row: Use the following array formula (entered with
CTRL+SHIFT+ENTER)
=MIN(IF((A1:A500=DATE(2004,1,1))*(B1:B500=1),ROW(A1:A500)))

2. Last row (also array entered):
=MAX(IF((A1:A500=DATE(2004,1,1))*(B1:B500=1),ROW(A1:A500)))


Lets say you have inserted thse formulas in cells E1 and F1 then use
for example the following formula to sum column D for this range:
=SUM(INDIRECT("D" & E1 & ":D" & F1))

or use
=SUM(OFFSET(D1,E1-1,0,F1-E1+1))
 
Back
Top