SUMIF or SUMPRODUCT with multiple criteria across rows and columns

[jbrouse]

I asked in a previous post (http://www.mrexcel.com/forum/showthread.php?t=569058) how to use SUMPRODUCT for criteria across rows and columns, and was told to do something like this instead:

=SUMIF('Sales Plan'!$D$2:$D$1876,'Sheet1'$D7,INDEX('Sales Plan'!$G$2:$R$1876,0,MATCH(AH$1,'Sales Plan'!$G$1:$R$1,0)))

which will sum values in the correct column for which 'Sales Plan!, Column D = Sheet1!D7

The problem is, I need more criteria.

I need 'Sales Plan'!$A$2:$A$1876 to be equal to something, and I need 'Sales Plan'!$A$2:$A$1876 to be equal to something. Both will have a static reference, let's say 'Sheet1'$A$1 and $A$2, respectively.

Can someone please help me out?

Thanks in advance!

 

[jbrouse]

Yes, that is correct.

 

[jbrouse]

And that's why it won't work, isn't it? Because by using the dynamic named range, I'm perhaps using a different number of rows then all of the other conditions?

 

[Aladin Akyurek]

And that's why it won't work, isn't it? Because by using the dynamic named range, I'm perhaps using a different number of rows then all of the other conditions?

Yes, that's probably the culprit. Try the following set up...

Define Lrow as referring to:

=MATCH(9.99999999999999E+307,'Sales Plan'!$A:$A)

PartNoLookup as referring to:

='Sales Plan'!$A$2:INDEX('Sales Plan'!$A:$A,Lrow)

Brange (or any other more appropriate name) as referring to:

='Sales Plan'!$B$2:INDEX('Sales Plan'!$B:$B,Lrow)

Drange (or any other more appropriate name) as referring to:

='Sales Plan'!$D$2:INDEX('Sales Plan'!$D:$D,Lrow)

GRrange or any other more appropriate name) as referring to:

='Sales Plan'!$G$2:INDEX('Sales Plan'!$R:$R,Lrow)

and finally GRheader as refering to:

='Sales Plan'!$G$1:$R$1

Now you can invoke...

Either:

=SUMIFS(
     INDEX(GRrange,0,MATCH(AH$1,GRheader,0))),
       ParNoLookup,Sheet1!$A$1,
       Brange,Sheet1!$A$2,
       Drange,Sheet1!$D7)

Or:

=SUMPRODUCT(
    INDEX(GRrange,0,MATCH(AH$1,GRheader,0))),
    --(PartNoLookup=Sheet1!$A$1),
    --(Brange=Sheet1!$A$2),
    --(Drange=Sheet1!$D7))

 

[jbrouse]

Hello.

I know it has been a long time, but I wanted to thank you for all of your help. I went on vacation right after all of this and am just now getting back. Your solution worked perfectly and I very much appreciate your assistance.

Thanks again!

 

[Aladin Akyurek]

Hello.

I know it has been a long time, but I wanted to thank you for all of your help. I went on vacation right after all of this and am just now getting back. Your solution worked perfectly and I very much appreciate your assistance.

Thanks again!

You are welcome. Thanks for providing feedback.