|
SUMIF(AND) FUNCTION
Hi Frank,
I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
=SUMPRODUCT((A1:A8="Europe")*(MONTH(B1:B8)=10)*(C1 :C8)) should do it -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273 View this thread: http://www.excelforum.com/showthread...hreadid=222618 |
Hi,
How about if you add a column after the date and include the formula MONTH(A2) that returns the month number then use the following formula: A = dealer B = date C = month number D = sale =SUMPRODUCT(--(A2:A9=A13),--(C2:C9=B13),D2:D9) You'd then need to enter 9 instead of September in you other sheet as the matching criteria. Good luck, Rob "Saariko" wrote in message ... Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Hi,
If you want the sales for Asia region for the month of September, use =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000)) Regards Govind. Saariko wrote: Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Hi again,
I wen to the xLDunamic help page: Here is another unsuccessful try: =SUMPRODUCT((A170:A180="Asia")*(MONTH(ROW(B179:B18 0))=9)*(C170:C180)) please? "Saariko" wrote: Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Thanks to all. (Alex, Rob)
Govind's answer has made it happen:-) Sheers, Saariko "Govind" wrote: Hi, If you want the sales for Asia region for the month of September, use =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000)) Regards Govind. Saariko wrote: Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Thanks All (Alex, Rob)
I used govinds answer, it worked. Cheers, Saariko "Govind" wrote: Hi, If you want the sales for Asia region for the month of September, use =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000)) Regards Govind. Saariko wrote: Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Here are my comments:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) Won't work because "9/28/2004" is text not Date Value for Excel. You should use date serial number, in this case 38258, so that the formula looks as follows =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000=38258)). The formula only COUNTS(!!!) exact DATE matches where DEALER is "Asia". =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) Works like charm to me. See if list separator on your system is set to ";" and then change "," by ";". The formula only COUNTS(!!!) exact MONTH matches where DEALER is "Asia". =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) Wrong sintax. MONTH(B3):MONTH(B10000)=10 makes no sense and will not work. what you probably need is the following: =SUMPRODUCT($C$3:$C$1000,--($A$3:$A$10000="Asia"),--(MONTH($B$3:$B$10000)=10)) The formula SUMS(!!!) SALES, where there are exact MONTH matches combined with "Asia" for DEALER. "Saariko" wrote in message ... Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
Saariko Wrote: [...] I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? [...] Let A1:D9 on Sheet1 house the dealer data. Let A2:B2 on Sheet2 house the conditions of interest. In C2 on Sheet2 enter & copy down: =SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$9,"dddd")=A2),--(Sheet1!$A$2:$A$9=B2),Sheet1!$C$2:$C$9) if the year must be explicitly excluded. Otherwise, ensure that A2 houses a true date, 9/1/2004, formatted to show, for example: Sep-04 using mmm-yy as custom format. Change the formula to include the year test as follows: =SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$9,"mmm-yy")=TEXT(A2,"mmm-yy"),--(Sheet1!$A$2:$A$9=B2),Sheet1!$C$2:$C$9) -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=222618 |
Hi,
You can use =SUMPRODUCT((A170:A180="Asia")*(MONTH(B170:B180)=9 )*(C170:C180)) Regards Govind. Saariko wrote: Hi again, I wen to the xLDunamic help page: Here is another unsuccessful try: =SUMPRODUCT((A170:A180="Asia")*(MONTH(ROW(B179:B18 0))=9)*(C170:C180)) please? "Saariko" wrote: Hi Frank, I usually use your replies with blind eyes, and Bingo. However, I have tried this solution of multiple arrays and condition, and a little problem for me on this one. I have 3 colums: A-dealer B-Date C-sale I want to sum (Pivot Table is not suitable for this part, I use it on a different worksheet) All sales made be a dealer based on a month. Asia 9/28/2004 2134 Asia 9/30/2004 2136 Europe 10/1/2004 2137 Europe 10/4/2004 2138 Europe 10/12/2004 2139 Europe 10/12/2004 2140 Asia 10/17/2004 2141 Europe 10/18/2004 2142 The sum would go to a different sheet MONTH Dealer Total Sales September Asia ????? October Europe ????? Again, I can't use pivot table for this part. I tried to tweak your funciton: =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004")) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10)) =SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10)) but no luck. Can you help? "Frank Kabel" wrote: Hi first don't replace the '--'. They coerce the boolean values to real numbers (TRUE=1/FALSE=0) for your example if you want to get the sum for one specific account use =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D 1:D4000="michigan"),F1:F4000) Note: this is case sensitive. You may also consider using a pivot table for this: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany dave wrote: Frank, Sorry, I got an error. What do I put in where the "--" are? Here is a more accurate example of my worksheet. Column A lists a range of account numbers so I'd like this range to find all 4000 accounts. Column C lists a range of departments so I'd like to find "collections" column D lists the location so I want "michigan" Column F has the balances so I want the sum of every account that meets these 3 criteria. Thanks, Dave -----Original Message----- Hi use SUMPRODUCT instead. e.g. =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100) -- Regards Frank Kabel Frankfurt, Germany Dave Gremaud wrote: Does anyone use this function or have any suggestions on a different function that may work. I have a very large worksheet with multiple data columns. I am trying to sum multiple cells by using multiple variables. The SUMIF function works well but I can't insert more than one criteria. I have tried many times without success. I would greatly appreciate any assistance. Here is an example of my need: I would like a function to read (if column "a" = 100 and column "b" = "manager" then sum these fields). I know there must be a solution but it escapes me. Thanks! Dave . |
| All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com