Sumif with an array for criteria

[Chris]

Hello, I am trying to write a sum if array formula that will look up against
multiple criteria and then finally compare to an array...here is an example
of what I wrote:

{=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Sheet2!$C$12:$C$10000=(Lots!$H$6:$H$18),Sheet2!$K$12:$K$10000,0),0))}

The formula works fine if I take out the $H$18, but as soon as I try to
match that, it will not work...

So to clarify what I am trying to do:

Sheet 2 contains my data:
Column A is weeks -
Column K is Sales - (what I want to sum)
Column C is SKU ID

Sheet 3 is the Form I am summing to.
Cell B7 is a specific week I am trying to sum for

Lots Sheet contains a list of sku ID's

Basically the formula is written like this:

Sum(if(the week on sheet 2 = the desired week on sheet 3,(if the sku id on
sheet 2 = the list of sku's on lots sheet, then sum the sales on sheet 2 in
column K

Is there a way to write this? Thanks.

 

[T. Valko]

{=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Sheet2!$C$12:$C$10000=(Lots!$H$6:$H$18),Sheet2!$K$12:$K$10000,0),0))}

Try this normally entered version...

=SUMPRODUCT(--(Sheet2!$A$12:$A$10000=Sheet3!$B7),--(ISNUMBER(MATCH(Sheet2!$C$12:$C$10000,Lots!$H$6:$H$18,0))),Sheet2!$K$12:$K$10000)

 

[Chris]

Thanks....this worked...never used Sumproduct before...will need to read how
it works.

 

[T. Valko]

You're welcome. Thanks for the feedback!

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html