Sumproduct - Multiple Criteria

[Rick]

Hi,

I have the below formula, for a particular product add up sales in column M
for 3 criteria in column C then subtract another criteria from it. There are
several other codes in Column C I am trying to exclude but the formula seems
to add everything then subtract the other criteria.

How do I get the formula to only add up the required 3 not ALL of them
before subtracting the other.

Hope you can read the formula, I am using 2007. I have tried using as an
array but doesn't make any difference.

=SUMPRODUCT(('October NR Sales invoices 2'!$M$3:$M$104551)*--OR(('October NR
Sales invoices 2'!$C$3:$C$104551='October NR Sales invoices
2'!$C$104556),('October NR Sales invoices 2'!$C$3:$C$104551='October NR Sales
invoices 2'!$C$104561),('October NR Sales invoices 2'!$C$3:$C$104551='October
NR Sales invoices 2'!$C$104562))*('October NR Sales invoices
2'!$K$3:$K$104551=$A3))-SUMPRODUCT(('October NR Sales invoices
2'!$M$3:$M$104551)*--('October NR Sales invoices 2'!$C$3:$C$104551='October
NR Sales invoices 2'!$C$104565)*--('October NR Sales invoices
2'!$K$3:$K$104551=$A3))

Thanks
Rick

 

[T. Valko]

Put the values of your "OR" criteria in a contiguous range of cells.

B1: ='sheetname'!C104556
B2: ='sheetname'!C104561
B3: ='sheetname'!C104562

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH('sheetname'!C3:C104551,B1:B3,0))),--('sheetname'!K3:K104551=A3),'sheetname'!M3:M104551)-SUMPRODUCT(--('sheetname'!C3:C104551='sheetname'!C104565),--('sheetname'!K3:K104551=A3),'sheetname'!M3:M104551)

 

[Rick]

Thanks worked great.

Put the values of your "OR" criteria in a contiguous range of cells.

B1: ='sheetname'!C104556
B2: ='sheetname'!C104561
B3: ='sheetname'!C104562

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH('sheetname'!C3:C104551,B1:B3,0))),--('sheetname'!K3:K104551=A3),'sheetname'!M3:M104551)-SUMPRODUCT(--('sheetname'!C3:C104551='sheetname'!C104565),--('sheetname'!K3:K104551=A3),'sheetname'!M3:M104551)

 

[T. Valko]

You're welcome. Thanks for the feedback!