Google Docs Spreadsheet display data from row matching a criteria
Basically, I would like Sheet 1 to pull data FROM Sheet 2, renamed to 'Loot'. However, the data that is pulled from 'Loot' can only be from a specific column, and two other cells in the same row must contain specific data from Sheet 1.
Example: 'Loot' has the following important Columns for B, C, and D: Character, Item Type, and Quantity
I want a cell in Sheet 1 to contain a Quantity(D) value from 'Loot', but only if the Character(B) and Item Type(C) cells that share the same row as the data that is being pulled matches that of two specific cells on Sheet 1.
This is as close as I could come without success.
=QUERY('Loot'!D2:D;"select * where (B matches A14 and C matches A8)";1)
Here is a temporary link to the spreadsheet. https://docs.google.com/spreadsheet/ccc?key=0AgUUnVW8yWhXdHhsZnpac29LdjdhTG9adFg4NnhwVHc&usp=sharing
Sheet 1 is Veldspar
Sheet 2 is Loot
With the help of a friend, I'be been able to get this far...
=QUERY('Loot'!B2:D100,"select (D) where B = '" & A14 & "' and C = '" & A8 &"'")
The problem is that if there is more than one result, instead of adding it together, it just displays each result in each cell below it
EDIT
Fixed all the issues and found another one.
=SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'"))
The problem now is that if there is no criteria to pull data from, the cell produces a #N/A error which causes all other cells that reference its data to be unable to calculate their data.
EDIT..
So far so good, this is what I used to fix the issue
=IFERROR(SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'")),"0")
EDIT..
That last one edit the trick. My problem has been solved. I left the link available to all and just changed all the names located in the Loot 'sheet' in order to hide names.
Answer
Answering my own question :)
=IFERROR(SUM(QUERY('Loot'!B$2:D,"select (D) where B matches '"& $A14 &"' and C matches '" & A$8 &"'")),"0")