Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single statement database/range processing with criteria
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single statement database/range processing with criteria
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Single statement database/range processing with criteria
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 "--" outside the parentheses? What does it stand for? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement using multiple criteria | Excel Worksheet Functions | |||
How do I use a "check(ed) box" as a criteria in an "=if" statement | Excel Worksheet Functions | |||
Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
If Statement Criteria | Excel Worksheet Functions |