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

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

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

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

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