Using Sum, index, and Match. Please help

[lo1ly]

Hi Everyone,

I'm trying to use a standard Index(match,Match) equation, but I want to sum multiple values. I am only able to pull the first number that meets it's criteria. How do I sum ALL the numbers that fit the criteria

PLEASE HELP! let me know if you need anymore information

Here is an example below

Problem: Find the sum of all numbers That are BLUE in column B for week2 (Which is 9,5, and 3 = 17)

This is the Equation that I'm using but its only Pulling back the first value of Blue and week 2... which is 9

Sheet 1 (where I want answer)
[TABLE="class: grid, width: 5"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BLUE[/TD]
[TD]Sum(Index(Sheet2!1:100,Match(A2,Sheet2!A;A,0),Match(B1,Sheet2!1:1,0)))[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2 (data)[TABLE="class: grid, width: 5"]
<tbody>[TR]
[TD][/TD]
[TD] A[/TD]
[TD] B[/TD]
[TD] C[/TD]
[TD] D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Red[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Red[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Red[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Red[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Blue[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Blue[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Green[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Green[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

---

 

[Jonmo1]

Try
=SUMIF(Sheet2!$A:$A,$A2,INDEX(Sheet2!$B:$D,0,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

 

[lo1ly]

That worked! thank you!

 

[Jonmo1]

You're welcome.