R
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
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