If you want to extract the records or hide non conforming records, then it
would be unlikely that a worksheet function would be the usual approach. If
you want to sum the prices

=Sumproduct(--(Item=Search_Item),--(Price_Valid_From<=Search_Date),--(Price_
Valid_To=Search_Date),Prices))

=Sumproduct(--(Year(DateColumn)=2006),--(Trans_Type="Sales"),Amount)


These could be defined names (insert=Name=Define) or replaced by actual
ranges.

--
Regards,
Tom Ogilvy



"DoctorG" wrote in message
...
Although Excel provides Database processing (DSUM etc) it does so with
"external" criteria - "external" meaning that they are defined in cells
outside the Database function.

I have seen posts that process ranges with really complex criteria defined
in a single statement. Having all search conditions within the cell
statement
is preferable as far as I am concerned as it results in less messy
spreadsheets. Can anyone provide links to articles describing ways to
process
ranges/databases with criteria such as the following in a single
statement?

Capitalized words represent Fieldnames (column headings).

- find in PRICES_DATABASE the PRICE where ITEM = Search_Item and
Search_Date
is = to PRICE_VALID_FROM and <= to PRICE_VALID_TO

- sum all AMOUNT in TRANSACTIONS_DATABASE where YEAR = 2006 and TRNS_TYPE
=
"Sales"

Thanks in advance