EXCEL cell reference problem

[rookiesql]

Hi!
I have a big excel sheet with a lot of data that use to present various statistics. Each record is a row and the are sorted after creation date which is one of several columns.
Now I wanted to do the following: search one of the columns for specific contents (for example date (not creation date) within a certain range)and then offset the resulting list of cells to select the corresponding cells in another column, that shall be used for a calculation. The cells that fulfill the condition to be in a certain date range does not make up a consequtive cell range.

I know how to select and offset consequtive cell ranges, but not non-consequtive.

Maybe someone knows how to do?

Regards
Anders

 

[GlennUK]

Have a search for past threads using SUMPRODUCT which should give you some good examples.

Glenn.

 

[rookiesql]

Hi,
I looked in the SUMPRODUCT threads but couldn't see the solution. To be more specific about my problem:
My data (which is customer error reports) looks basically like this:
C1 C2 C3 C4
id submit date solution date time2solution

I want to calculate the PERCENTILE value of time2solution (C4) for all the error report id that has a solution date (C3) within a certain range. The data is sorted ascending after C2, but this does not necessarily mean that C3 is sorted.
I need an excel function that return the references to cells in C3 which have values in the date range and then offset these to the corresponding cells in C4 and use these for my percentile calculation. I can't see how SUMPRODUCT can be used, maybe because it is Friday afternoon...
/Anders