Multiple Columns With the Same Items to Sum

[AKFlyer]

Hi, I'm trying to find a way to sum the amount of needed materials based on what Item(s) I choose/filter by. In other words, if I want to build Items 1 and 5 in the first box below, I would need a total of 1 Copper, 3 Silver, 2 Iron, 6 Gold. I want to choose/filter by the items I'm looking for, and have it display only one instance of each material and the sum of that material I need. Something like the second box below.

I've tried Pivot Table, Sumifs, Filtering, and a couple other dumb ideas but can't seem to get it to work. I'd say I'm above average on my Excel knowledge but some things just stump me. I appreciate any help or suggestions!

 

[kevin9999]

if I want to build Items 1 and 5 in the first box below

Were you meant to attach an image or something?

 

[AKFlyer]

Yeah, sorry. I'm a dummy.

 

[kevin9999]

I'm sure there'll be a formula answer to this, but this is the best I could come up with. Uses a UDF, and conditional formatting.
UDF (pu in a standard module)

Function SumMat(r As Range, s As String) As Long
    Dim c As Range, x As Long
    For Each c In r
        If c.EntireRow.Hidden = False And c = s Then x = x + c.Offset(0, 1)
    Next c
    SumMat = x
End Function

Before applying the filter:

Book1
	A	B	C	D	E	F	G
1	ITEM	Material	Amt	Material	Amt	Material	Amt
2	Item 1	Copper	1	Silver	2
3	Item 2	Silver	3	Gold	1	Iron	1
4	Item 3	Iron	2	Nickel	5	Silver	2
5	Item 4	Nickel	2	Copper	1
6	Item 5	Silver	1	Iron	2	Gold	6
7
8
9		Material	Amt
10		Copper	2
11		Gold	7
12		Iron	5
13		Nickel	7
14		Silver	8
15
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=SumMat($B$2:$G$6,B10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B14	Expression	=C10=0	text	NO
C10:C14	Cell Value	=0	text	NO

After applying the filter:

Book1
	A	B	C	D	E	F	G
1	ITEM	Material	Amt	Material	Amt	Material	Amt
2	Item 1	Copper	1	Silver	2
6	Item 5	Silver	1	Iron	2	Gold	6
7
8
9		Material	Amt
10		Copper	1
11		Gold	6
12		Iron	2
13		Nickel	0
14		Silver	3
15
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=SumMat($B$2:$G$6,B10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B14	Expression	=C10=0	text	NO
C10:C14	Cell Value	=0	text	NO

 

[AKFlyer]

I'm sure there'll be a formula answer to this, but this is the best I could come up with. Uses a UDF, and conditional formatting.
UDF (pu in a standard module)

Function SumMat(r As Range, s As String) As Long
    Dim c As Range, x As Long
    For Each c In r
        If c.EntireRow.Hidden = False And c = s Then x = x + c.Offset(0, 1)
    Next c
    SumMat = x
End Function

Before applying the filter:

Book1
	A	B	C	D	E	F	G
1	ITEM	Material	Amt	Material	Amt	Material	Amt
2	Item 1	Copper	1	Silver	2
3	Item 2	Silver	3	Gold	1	Iron	1
4	Item 3	Iron	2	Nickel	5	Silver	2
5	Item 4	Nickel	2	Copper	1
6	Item 5	Silver	1	Iron	2	Gold	6
7
8
9		Material	Amt
10		Copper	2
11		Gold	7
12		Iron	5
13		Nickel	7
14		Silver	8
15
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=SumMat($B$2:$G$6,B10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B14	Expression	=C10=0	text	NO
C10:C14	Cell Value	=0	text	NO

After applying the filter:

Book1
	A	B	C	D	E	F	G
1	ITEM	Material	Amt	Material	Amt	Material	Amt
2	Item 1	Copper	1	Silver	2
6	Item 5	Silver	1	Iron	2	Gold	6
7
8
9		Material	Amt
10		Copper	1
11		Gold	6
12		Iron	2
13		Nickel	0
14		Silver	3
15
Sheet1

Cell Formulas
Range	Formula
C10:C14	C10	=SumMat($B$2:$G$6,B10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B10:B14	Expression	=C10=0	text	NO
C10:C14	Cell Value	=0	text	NO

This works for what I need, thank you!

 

[kevin9999]

This works for what I need, thank you!

Happy to help, and thanks for the feedback