Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate row based on two criteria
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.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate row based on two criteria
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.... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate row based on two criteria
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.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate row based on two criteria
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.... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Locate row based on two criteria
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locate a cell, based on a criteria, then use the 'Cell' command... | Excel Discussion (Misc queries) | |||
Locate a Cell based upon criteria | Excel Discussion (Misc queries) | |||
Locate max value of one column based on criteria in another colum | Excel Worksheet Functions | |||
MIN within range based on criteria | Excel Discussion (Misc queries) | |||
Need help looking up value based on criteria | Excel Worksheet Functions |