Vlookup wih multiple conditions and repetations

[aayush_agarwal]

Hi everyone,
I am new to excel codng and require your help in solving the problem i am facing.
My data set contains different project (10 in total) with each project containing multiple materials (M1,M2,M3,M4...) under it. In front of each material is its corresponding volume. Note that inside a project the name of material may be repeated multiple number of times. My dataset looks similar to the data in the snapshot attached in the pic.

Sheet 1 : Input
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Material[/TD]
[TD][/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V1[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V2[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V3[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V4[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V6[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V7[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V8[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V9[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V10[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V11[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V12[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V13[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V14[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V15[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V16[/TD]
[/TR]
</tbody>[/TABLE]

I was looking for a formula or VBA code which would calculate the total volume corresponding to each material (M1,M2,M3,M4...) for each project (Project 1, project 2, project 3....). The output required in sheet 2 should look similar to the table shown below :
Output in sheet 2 :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Material[/TD]
[TD][/TD]
[TD]Total Volume[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V1+V5[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V2+V3[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V6+V8[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V4+V7[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V9+V13+V16[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V10+V11[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V14[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]M4[/TD]
[TD][/TD]
[TD]V12+V15[/TD]
[/TR]
</tbody>[/TABLE]

I have understood that i will have to use Vlookup formula with 2 search criteria, however i dont know how to take all volumes of a material inside a project. Fore example, in project 1 material M1 is repeated 2 times with respective volume V1 and V5. I want the output to be V1+V5 whereas Vlookup gives only the first occurance that is V1.
It would be great if you can give a formula or VBA code for solving this problem.
Thanks a lot for your help.

 

[Legacy 143009]

Not VLOOKUP but looks like SUMIFS.
Sheet2 D2 =SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!$A2,Sheet1!$B:$B,Sheet2!$B2)

 

[aayush_agarwal]

@Flashbond Thanks for the input. Is it possible to give multiple disjoint columns as input instead of only one column B:

[COLOR=#333333]Sheet2 D2 =SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,Sheet2!$A2,(Look across multiple columns),Sheet2!$B2)[/COLOR]

The criteria_range2 in my case is a set of 3 disjoint columns.
Is i possible to modify the code to incorporate ths ?
Thanks a lot.

 

[Legacy 143009]

I don't understand. Can you post some sample data?