I have a list of data where column A and Column B together identify the row
uniquely.

From a formula I want to locate a specific row matching the TWO criteria
(i.e. both value in A and B columna should match my input values). I want to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location C-Stock Qty
Barbie doll X 10
Barbie doll Y 6
Barbie doll Z 3
Spiderman X 2
Spiderman Y 4

I want to find how many barbie dolls are on location Y (=6) and use this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one criteria
I would have used VLOOKUP......

Grateful for any help on this....

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
I have a list of data where column A and Column B together identify the row
uniquely.

From a formula I want to locate a specific row matching the TWO criteria
(i.e. both value in A and B columna should match my input values). I want
to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location C-Stock
Qty
Barbie doll X 10
Barbie doll Y 6
Barbie doll Z 3
Spiderman X 2
Spiderman Y 4

I want to find how many barbie dolls are on location Y (=6) and use this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one
criteria
I would have used VLOOKUP......

Grateful for any help on this....

The function always returns zero since the entries are not numeric. I want to
return the exact value of column C, and I'm not sure I understand how
sumproduct can do that.. Or am I missing something ?
Kathrine

"Sandy Mann" wrote:

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
I have a list of data where column A and Column B together identify the row
uniquely.

From a formula I want to locate a specific row matching the TWO criteria
(i.e. both value in A and B columna should match my input values). I want
to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location C-Stock
Qty
Barbie doll X 10
Barbie doll Y 6
Barbie doll Z 3
Spiderman X 2
Spiderman Y 4

I want to find how many barbie dolls are on location Y (=6) and use this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one
criteria
I would have used VLOOKUP......

Grateful for any help on this....

I get 6 whether column C has text number or real numbers. The other colums
do not need to be numbers for SUMPRODUCT() to work. See

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

for an explanation of this use of SUMPRODUCT()

If you want you can send me a sample of your spreadsheet and I will have a
look to see whay it is not working for you.

Correct my address as it says in my signature by replacing the
@maininator.com which is a spam trap.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
The function always returns zero since the entries are not numeric. I want
to
return the exact value of column C, and I'm not sure I understand how
sumproduct can do that.. Or am I missing something ?
Kathrine

"Sandy Mann" wrote:

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
I have a list of data where column A and Column B together identify the
row
uniquely.

From a formula I want to locate a specific row matching the TWO
criteria
(i.e. both value in A and B columna should match my input values). I
want
to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location
C-Stock
Qty
Barbie doll X
10
Barbie doll Y
6
Barbie doll Z
3
Spiderman X 2
Spiderman Y
4

I want to find how many barbie dolls are on location Y (=6) and use
this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one
criteria
I would have used VLOOKUP......

Grateful for any help on this....

You are right. It works! I was using the function wizard. It puts ; in
between the arguments. Replacing with * as in your example did it!

Thank you!

"Sandy Mann" wrote:

I get 6 whether column C has text number or real numbers. The other colums
do not need to be numbers for SUMPRODUCT() to work. See

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

for an explanation of this use of SUMPRODUCT()

If you want you can send me a sample of your spreadsheet and I will have a
look to see whay it is not working for you.

Correct my address as it says in my signature by replacing the
@maininator.com which is a spam trap.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
The function always returns zero since the entries are not numeric. I want
to
return the exact value of column C, and I'm not sure I understand how
sumproduct can do that.. Or am I missing something ?
Kathrine

"Sandy Mann" wrote:

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
I have a list of data where column A and Column B together identify the
row
uniquely.

From a formula I want to locate a specific row matching the TWO
criteria
(i.e. both value in A and B columna should match my input values). I
want
to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location
C-Stock
Qty
Barbie doll X
10
Barbie doll Y
6
Barbie doll Z
3
Spiderman X 2
Spiderman Y
4

I want to find how many barbie dolls are on location Y (=6) and use
this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one
criteria
I would have used VLOOKUP......

Grateful for any help on this....

Hi Katherine,

I'm glad you got it sorted out. Using SUMPRODUCT() this way was never
envisaged by Microsoft, it was some of the clever people around here that
extended its use.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
You are right. It works! I was using the function wizard. It puts ; in
between the arguments. Replacing with * as in your example did it!

Thank you!

"Sandy Mann" wrote:

I get 6 whether column C has text number or real numbers. The other
colums
do not need to be numbers for SUMPRODUCT() to work. See

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

for an explanation of this use of SUMPRODUCT()

If you want you can send me a sample of your spreadsheet and I will have
a
look to see whay it is not working for you.

Correct my address as it says in my signature by replacing the
@maininator.com which is a spam trap.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
The function always returns zero since the entries are not numeric. I
want
to
return the exact value of column C, and I'm not sure I understand how
sumproduct can do that.. Or am I missing something ?
Kathrine

"Sandy Mann" wrote:

Try:

=SUMPRODUCT((A2:A6="Barbie doll")*(B2:B6="Y")*C2:C6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Kathrine" wrote in message
...
I have a list of data where column A and Column B together identify
the
row
uniquely.

From a formula I want to locate a specific row matching the TWO
criteria
(i.e. both value in A and B columna should match my input values). I
want
to
return the value in column C.

ex: (list is sorted - A asc, then B asc)
A- Item B-Item location
C-Stock
Qty
Barbie doll X
10
Barbie doll Y
6
Barbie doll Z
3
Spiderman X
2
Spiderman Y
4

I want to find how many barbie dolls are on location Y (=6) and use
this
value for further processing.

Should be simple, but I'm not very advanced in Excel. With only one
criteria
I would have used VLOOKUP......

Grateful for any help on this....