Formula to make sum based on multiple conditions, HELP!

ilsel · #1 11-14-2017, 02:23 AM

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!

NBVC · #2 11-14-2017, 06:14 AM

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)

ilsel · #3 11-14-2017, 01:55 PM

Quote:

Originally Posted by NBVC

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)

Thank you!
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?

ilsel · #4 11-15-2017, 02:03 AM

Quote:

Originally Posted by NBVC

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)

I found what I did wrong!
Thank you so much, lifesaver.
Greets Ilse.

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