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!

 

[Aladin Akyurek]

Do you mean that

'Sales Plan'!$A$2:$A$1876

is allowed to be equal to either Sheet1!A1 or Sheet1!A2?

 

[jbrouse]

'Sales Plan'!$A$2:$A$1876 should be equal to 'Sheet1'$A$1
'Sales Plan'!$B$2:$B$1876 should be equal to 'Sheet1'$A$2

Sorry. I'm typing faster than my brain is working.

Thanks!

 

[Aladin Akyurek]

'Sales Plan'!$A$2:$A$1876 should be equal to 'Sheet1'$A$1
'Sales Plan'!$B$2:$B$1876 should be equal to 'Sheet1'$A$2

Sorry. I'm typing faster than my brain is working.

Thanks!

If you are on Excel 2007 or later...

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

Otherwise:

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

 

[jbrouse]

Works great! Thank you so much!

 

[Aladin Akyurek]

Works great! Thank you so much!

Glad to help. Thanks for providing feedback.

 

[jbrouse]

Actually, I have one last question.

I have a dynamic named range called PartNoLookup which refers to

=OFFSET('Sales Plan'!$D$2,0,0,COUNTA('Sales Plan'!$D:$D),1), basically the same range as 'Sales Plan'!$D$2:$D$1876 with the ability to expand.

How come if I replace 'Sales Plan'!$D$2:$D$1876 with PartNoLookup, I get a #VALUE error?

 

[Aladin Akyurek]

Actually, I have one last question.

I have a dynamic named range called PartNoLookup which refers to

=OFFSET('Sales Plan'!$D$2,0,0,COUNTA('Sales Plan'!$D:$D),1), basically the same range as 'Sales Plan'!$D$2:$D$1876 with the ability to expand.

How come if I replace 'Sales Plan'!$D$2:$D$1876 with PartNoLookup, I get a #VALUE error?

Which range on Sales Plan is numeric and the most complete?

 

[jbrouse]

Any of columns A through F in 'Sales Plan' will have the same number of rows, guaranteed to have data, but only Column A is guaranteed to be numeric.

 

[Aladin Akyurek]

Any of columns A through F in 'Sales Plan' will have the same number of rows, guaranteed to have data, but only Column A is guaranteed to be numeric.

The formula refers to:

'Sales Plan'!$G$2:$R$1876
'Sales Plan'!$A$2:$A$1876
'Sales Plan'!$B$2:$B$1876
'Sales Plan'!$D$2:$D$1876

Is the range in column A numeric and expected to be most complete at any time?