Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - Multiple Criteria
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - Multiple Criteria
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 ) -- Biff Microsoft Excel MVP "Rick" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - Multiple Criteria
Thanks worked great.
"T. Valko" wrote: 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 ) -- Biff Microsoft Excel MVP "Rick" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct - Multiple Criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Rick" wrote in message ... Thanks worked great. "T. Valko" wrote: 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 ) -- Biff Microsoft Excel MVP "Rick" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct-multiple criteria = and not = | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
HELP...SUMPRODUCT with multiple criteria | Excel Discussion (Misc queries) | |||
Sumproduct multiple criteria | Excel Worksheet Functions | |||
sumproduct using multiple criteria | Excel Worksheet Functions |