Sum values on another sheet using multiple criteria

[ScottM25]

Hi all,
First time posting here.
I don't think what I'm trying to do is too complicated but for the life of me I can't figure it out!

I have an excel workbook with 2 sheets.
One sheet has a list of items and critera:
Column A = Item Name
Column B = ItemType (Unique or Stock)
Column C = Item Status (Required (N) /Not required (Y))
Column D = Item Source.

On another table on Sheet 2, I have a list of the item names, and their cost.
COLUMN A = Item Name
Column B = Source 1
Column C = Source 2 etc

What I'm trying to do is add up the total cost of the items based on the item Type, Item Status and Item Source.
I have managed to achieve this using multiple VLOOKUP and IF statements, but the formula is clunky and I'm sure there must be a way to do this without it being so messy.

My current formula is:

=IF(AND(B3="Unique",C3="N",D3=$A$22),VLOOKUP(A3,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B4="Unique",C4="N",D3=$A$22),VLOOKUP(A4,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B5="Unique",C5="N",D3=$A$22),VLOOKUP(A5,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B6="Unique",C6="N",D3=$A$22),VLOOKUP(A6,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B7="Unique",C7="N",D3=$A$22),VLOOKUP(A7,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B8="Unique",C8="N",D3=$A$22),VLOOKUP(A8,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B9="Unique",C9="N",D3=$A$22),VLOOKUP(A9,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B10="Unique",C10="N",D3=$A$22),VLOOKUP(A10,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B11="Unique",C11="N",D3=$A$22),VLOOKUP(A11,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B12="Unique",C12="N",D3=$A$22),VLOOKUP(A12,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B13="Unique",C13="N",D3=$A$22),VLOOKUP(A13,Reference!$A$4:$E$12,2,FALSE),0)
+IF(AND(B14="Unique",C14="N",D3=$A$22),VLOOKUP(A14,Reference!$A$4:$E$12,2,FALSE),0)

I have tried a nested SUM and Lookup but I can't figure out how to make it work with multiple criteria and it just returns the total cost from the Source Column without the matching criteria.
I also tried SUMPRODUCT but I can't seem to get that to work across multiple sheets.

Any help would be greatly appreciated!

 

[Joe4]

I think it would be helpful if you could post a simple example of your data, and then your expected results based on your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.