Database function / formula question

  • Thread starter Thread starter Arnie
  • Start date Start date
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
 
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)
 
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
 
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?
 
Back
Top