software

Although Excel provides Database processing (DSUM etc) it does so with
quot;externalquot; criteria - quot;externalquot; 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 gt;= to PRICE_VALID_FROM and lt;= to PRICE_VALID_TO

- sum all AMOUNT in TRANSACTIONS_DATABASE where YEAR = 2006 and TRNS_TYPE =
quot;Salesquot;

Thanks in advanceIf 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_Fromlt;=Search_Date),--(Price_
Valid_Togt;=Search_Date),Prices))

=Sumproduct(--(Year(DateColumn)=2006),--(Trans_Type=quot;Salesquot;),Amount)These could be defined names (insert=gt;Name=gt;Define) or replaced by actual
ranges.

--
Regards,
Tom Ogilvy
quot;DoctorGquot; gt; wrote in message
...
gt; Although Excel provides Database processing (DSUM etc) it does so with
gt; quot;externalquot; criteria - quot;externalquot; meaning that they are defined in cells
gt; outside the Database function.
gt;
gt; I have seen posts that process ranges with really complex criteria defined
gt; in a single statement. Having all search conditions within the cell
statement
gt; is preferable as far as I am concerned as it results in less messy
gt; spreadsheets. Can anyone provide links to articles describing ways to
process
gt; ranges/databases with criteria such as the following in a single
statement?
gt;
gt; Capitalized words represent Fieldnames (column headings).
gt;
gt; - find in PRICES_DATABASE the PRICE where ITEM = Search_Item and
Search_Date
gt; is gt;= to PRICE_VALID_FROM and lt;= to PRICE_VALID_TO
gt;
gt; - sum all AMOUNT in TRANSACTIONS_DATABASE where YEAR = 2006 and TRNS_TYPE
=
gt; quot;Salesquot;
gt;
gt; Thanks in advance
gt;
Tom I don't want to extract or hide lines. I only need to sum values in a
database and locate a value (as is the price of an item on a given date).
Anyway, the summing method would be applicable to locate a value provided
there is only one matching record.

What I do not see in your answer, unfortunately, is a way to provide a
database range and the appropriate column headings (fieldnames ??) instead of
having to set up named ranges for every column I wish to use as a criterion.
I guess it's because we are using a range function instead of a database
function and only databases use the first row as field names. Any comment on
this is welcome.

I guess I'll stick to the SUMPRODUCT function. One last thing though...
When/why do we use the quot;--quot; outside the parentheses? What does it stand for?