Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database function / formula question
Hi,
Is it possible in excel te look in a database for more than one criterium? Example database (a1:d4) Name Date Product-X Product-Y John 1-1-2004 4 0 John 2-1-2004 1 0 Pete 10-1-2004 1 1 Find all sold products from: 1-1-2004 (cell b6) to: 7-1-2004 (cell b7 By John and Pete Output should look like this: Name Product-X Product-Y John 5 0 Pete 0 0 Thanx in advance, Arnie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database function / formula question
Arnie,
For Product X =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(C2:C4)) Product Y =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(D2:D4)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Arnie" wrote in message .nl... Hi, Is it possible in excel te look in a database for more than one criterium? Example database (a1:d4) Name Date Product-X Product-Y John 1-1-2004 4 0 John 2-1-2004 1 0 Pete 10-1-2004 1 1 Find all sold products from: 1-1-2004 (cell b6) to: 7-1-2004 (cell b7 By John and Pete Output should look like this: Name Product-X Product-Y John 5 0 Pete 0 0 Thanx in advance, Arnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database function / formula question
Is there any solution, if Product-X and Product-Y are not numeric, but
string (text)? Because SUMPRODUCT works only with numbers. In other words I would like to get a text value from a third column, that corresponds with the keys from first and second column Regards, Yakimoto "Bob Phillips" wrote in message ... Arnie, For Product X =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(C2:C4)) Product Y =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(D2:D4)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Arnie" wrote in message .nl... Hi, Is it possible in excel te look in a database for more than one criterium? Example database (a1:d4) Name Date Product-X Product-Y John 1-1-2004 4 0 John 2-1-2004 1 0 Pete 10-1-2004 1 1 Find all sold products from: 1-1-2004 (cell b6) to: 7-1-2004 (cell b7 By John and Pete Output should look like this: Name Product-X Product-Y John 5 0 Pete 0 0 Thanx in advance, Arnie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database function / formula question
Yakimo,
Actually Sumproduct does work because you are multiplying boolean values e.g., product-x = "widget". For a thorough explanation see this article by John Walkenbach: http://www.microsoft.com/office/prev...s/column10.asp hth, Doug "Yakimo" wrote in message ... Is there any solution, if Product-X and Product-Y are not numeric, but string (text)? Because SUMPRODUCT works only with numbers. In other words I would like to get a text value from a third column, that corresponds with the keys from first and second column Regards, Yakimoto "Bob Phillips" wrote in message ... Arnie, For Product X =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(C2:C4)) Product Y =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(D2:D4)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Arnie" wrote in message .nl... Hi, Is it possible in excel te look in a database for more than one criterium? Example database (a1:d4) Name Date Product-X Product-Y John 1-1-2004 4 0 John 2-1-2004 1 0 Pete 10-1-2004 1 1 Find all sold products from: 1-1-2004 (cell b6) to: 7-1-2004 (cell b7 By John and Pete Output should look like this: Name Product-X Product-Y John 5 0 Pete 0 0 Thanx in advance, Arnie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database function / formula question
Thanks Doug
The article is great, but it concerns variations of SUM and COUNT, i.e. the returning value is number. I couldn't see how to return a text Name Date Product-X Product-Y John 1-1-2004 widget 0 John 2-1-2004 gadget 0 Pete 10-1-2004 chair 1 I need to answer: "What is the Product-X sold by John" and the answer should be "widget" i.e. =func(John,1-1-2004) Result: widget As far as I can see SUMPRODUCT can calculate the number of sold product and sum of sold product. It seems I need some function as LOOKUP, but with two keys Any suggestions? "Doug Glancy" wrote in message ... Yakimo, Actually Sumproduct does work because you are multiplying boolean values e.g., product-x = "widget". For a thorough explanation see this article by John Walkenbach: http://www.microsoft.com/office/prev...s/column10.asp hth, Doug "Yakimo" wrote in message ... Is there any solution, if Product-X and Product-Y are not numeric, but string (text)? Because SUMPRODUCT works only with numbers. In other words I would like to get a text value from a third column, that corresponds with the keys from first and second column Regards, Yakimoto "Bob Phillips" wrote in message ... Arnie, For Product X =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(C2:C4)) Product Y =SUMPRODUCT((A2:A4="John")*(B2:B4=DATEVALUE("01-01-2004"))*(B2:B4<=DATEVALU E("07-1-2004")),(D2:D4)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Arnie" wrote in message .nl... Hi, Is it possible in excel te look in a database for more than one criterium? Example database (a1:d4) Name Date Product-X Product-Y John 1-1-2004 4 0 John 2-1-2004 1 0 Pete 10-1-2004 1 1 Find all sold products from: 1-1-2004 (cell b6) to: 7-1-2004 (cell b7 By John and Pete Output should look like this: Name Product-X Product-Y John 5 0 Pete 0 0 Thanx in advance, Arnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function/Formula Question | Excel Worksheet Functions | |||
Question on Formula Audit function | Excel Discussion (Misc queries) | |||
If function question in conditional formula | Excel Discussion (Misc queries) | |||
Formula/Function Question | Excel Discussion (Misc queries) | |||
formula/function question | Excel Worksheet Functions |