#1
|
|||
|
|||
Formula to make sum based on multiple conditions, HELP!
Hello everyone,
I'm making an inventory file. This file has a sheet with types of furniture T0001, T0002 and so on are the different types. And there is a sheet with unique codes that are assigned to each furniture piece, codes are ABC00001 and so on. In the CODES sheet '1' is placed in the room where the furniture piece is. I'm looking for a formula to paste in each cell in the TYPE sheet. So I have a sum of how many of a specific type of furniture is that room. I tried a SUM combined with VLOOKUP but didn't work. Who can help me? Example file in Example inventory.xlsx Have in mind in the actual file there are much more codes, rooms en types so one formula to copy trough the rows and coloms would be super! Sheet CODES.jpg Sheet TYPES.jpg Many thanks! |
#2
|
||||
|
||||
Try:
Code:
=SUMIFS(CODES!D$2:D$34,CODES!$C$2:$C$34,$B3) copied down and across. or if you need to also match the column headers because they may not be in consistent order, then Code:
=SUMIFS(INDEX(CODES!$D$2:$F$34,,MATCH(C$2,CODES!$D$1:$F$1,0)),CODES!$C$2:$C$34,$B3) |
#3
|
|||
|
|||
Quote:
It works in the example workbook but if I use it in the actual document it only gives '0'. The actual document is to large to upload here. https://www.dropbox.com/s/9w3zkkvm55...1114.xlsx?dl=0 Does this work? |
#4
|
|||
|
|||
Quote:
Thank you so much, lifesaver. Greets Ilse. |
Tags |
sum vlookup formula |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Merging 2 different cells containing IF formula & change in cell values based on multiple time frame | jay_excel | Excel | 0 | 07-29-2017 11:04 PM |
Index to return value based on 2 conditions | george batshon | Excel | 2 | 01-28-2017 05:11 AM |
How to make MATCH/INDEX formula return multiple successive responses. | danigirl121 | Excel | 7 | 06-08-2016 01:57 PM |
Excel Formula for Multiple Conditions & Results | nowco | Excel | 4 | 03-16-2016 11:24 AM |
How to Subtract value in table, based on two different conditions | nitemath2 | Excel | 1 | 06-19-2015 01:28 AM |