Sumifs with Array sum range..

[inincubus]

Attached file should be self-explanatory.

I want to be able to set sum range using lookup or match..
For example if I change B2 value to Nov-13, I want it to sum column K.

Thank you

Sumifs Range.xlsx

[daffodil11]

You could do it that way, but it would be an obtuse solution.

Can you use this instead:

B3:

=SUMPRODUCT(($G$3:$G$20=A3)*($H$3:$H$20=$E$19)*($I$2:$M$2=$B$2)*($I$3:$M$20))

and copied down

[ChemistB]

In B3 copied down
=SUMIF($G$3:$G$20,A3,INDEX($I$3:$M$20,,MATCH(B$2,$I$2:$M$2,0)))
Does that work for you?

[daffodil11]

Nice ChemistB. Never would have thought to use a nested INDEX.

So to get the Product ID, then:

=SUMIFS(INDEX($I$3:$M$20,,MATCH($B$2,$I$2:$M$2,0)),$G$3:$G$20,A3,$H$3:$H$20,$B$1)

and copied down

[ChemistB]

Thanks Daff

[inincubus]

Thank you both! I need to tweak the formula some bit for the actual data but I got the idea..!