Sum values on another sheet using multiple criteria

ScottM25

New Member
Joined
Apr 9, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,897
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,135
Messages
5,852,343
Members
431,497
Latest member
SteelheadBen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top