Database function / formula question

A

Arnie

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
 
B

Bob Phillips

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)
 
Y

Yakimo

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
 
Y

Yakimo

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?
 
Top