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(Sheet 2!$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.

{=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Shee t2!$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)

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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(Sheet 2!$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.

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

"T. Valko" wrote:

{=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Shee t2!$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)

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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(Sheet 2!$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.


.

You're welcome. Thanks for the feedback!

See this:

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

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
Thanks....this worked...never used Sumproduct before...will need to read
how
it works.

"T. Valko" wrote:

{=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Shee t2!$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)

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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(Sheet 2!$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.


.