|
SUMPRODUCT multiple criteria (with a twist)
How do I use SUMPRODUCT if one of the criteria arrays is the same as the
totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
I've never used SUMPRODUCT before, and I think I hate it!
No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
=SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000))
take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
Don't be concerned. You will learn to love SUMPRODUCT.
=SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
I don't love it yet, but I'm starting to warm up to it. Your solution worked
great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: I've never used SUMPRODUCT before, and I think I hate it! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
Gary,
Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
SUMPRODUCT multiple criteria (with a twist)
Hi,
Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD |
Sum Product Function
i need you help to use the sumproduct function
The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it 06-Jan-09 Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Create Function in SQL Server http://www.eggheadcafe.com/tutorials...in-sql-se.aspx |
Sum Product Function
Hi
Maybe you need to specify the range of source data, for example =SUMPRODUCT(--(Sheet1!E2:E11=H1),--(Sheet1!A1:A10=G2),Sheet1!F2:F11) -- Regards Roger Govier "noor hussain" wrote in message ... i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it 06-Jan-09 Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Create Function in SQL Server http://www.eggheadcafe.com/tutorials...in-sql-se.aspx __________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4830 (20100203) __________ The message was checked by ESET Smart Security. http://www.eset.com |
sumproduct
I am having some issues with sumproduct.
I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) noor hussain wrote: Sum Product Function 03-Feb-10 i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: On Wednesday, February 03, 2010 5:00 AM noor hussain wrote: Sum Product Function i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Submitted via EggHeadCafe - Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...ce-refere.aspx |
sumproduct
I am having some issues with sumproduct.
I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) noor hussain wrote: Sum Product Function 03-Feb-10 i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: On Wednesday, February 03, 2010 5:00 AM noor hussain wrote: Sum Product Function i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor On Thursday, March 04, 2010 2:34 PM robin l wrote: sumproduct I am having some issues with sumproduct. I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) Submitted via EggHeadCafe - Software Developer Portal of Choice Join Lists with LINQ - SharePoint 2010 http://www.eggheadcafe.com/tutorials...-linq--sh.aspx |
sumproduct
Either A has C* or M < B2, right?
Maybe... =SUMPRODUCT(((Detail!$A2:$A15000="C*")+(Detail!$M2 :$M15000<$B$2)0), (Detail!$L2:$L15000)) ps. C* really means the characters C, then asterisk. If you wanted the info that started with a C (using * as a wildcard): =SUMPRODUCT((left((Detail!$A2:$A15000,1)="C")+(Det ail!$M2:$M15000<$B$2)0), (Detail!$L2:$L15000)) ps. I think your original formula is incorrect--you just got lucky with your data. robin, l wrote: I am having some issues with sumproduct. I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) noor hussain wrote: Sum Product Function 03-Feb-10 i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: On Wednesday, February 03, 2010 5:00 AM noor hussain wrote: Sum Product Function i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Submitted via EggHeadCafe - Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...ce-refere.aspx -- Dave Peterson |
sumproduct
ps:
=SUMPRODUCT(((Detail!$A2:$A15000="C*")+(Detail!$M2 :$M15000<"")0), (Detail!$L2:$L15000)) should work too (A=C* or M<"") One more thought (after I reread your message)... It sounds like you want both to be true--an And, not an Or. =SUMPRODUCT(--(Detail!$A2:$A15000="C*"), --(Detail!$M2:$M15000<""), (Detail!$L2:$L15000)) or if that asterisk is a wildcard. =SUMPRODUCT(--(left(Detail!$A2:$A15000,1)="C"), --(Detail!$M2:$M15000<""), (Detail!$L2:$L15000)) robin, l wrote: I am having some issues with sumproduct. I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) noor hussain wrote: Sum Product Function 03-Feb-10 i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: On Wednesday, February 03, 2010 5:00 AM noor hussain wrote: Sum Product Function i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor Submitted via EggHeadCafe - Software Developer Portal of Choice Adding WCF Service References http://www.eggheadcafe.com/tutorials...ce-refere.aspx -- Dave Peterson |
I depend on it.
Thanks heaps, it works for me too.
GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. 06-Jan-09 Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: Previous Posts In This Thread: On Tuesday, January 06, 2009 1:14 PM g wrote: SUMPRODUCT multiple criteria (with a twist) How do I use SUMPRODUCT if one of the criteria arrays is the same as the totals array. I want to total all cells in column L that have a 1 in column D, as well as a negative amount in column L =SUMPRODUCT((D11:D60000="1")*(L11:L60000="<0")*(L1 1:L60000)) What am I doing wrong? I've never used SUMPRODUCT before, and I think I hate it! -- GD On Tuesday, January 06, 2009 1:24 PM T. Valko wrote: No, you actually love it but you just don't know that yet! No, you actually love it but you just don't know that yet! <g Try it like this: =SUMPRODUCT(--(D11:D60000=1),--(L11:L60000<0),L11:L60000) Everything you always wanted to know about SUMPRODUCT (and then some!): http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "GD" wrote in message ... On Tuesday, January 06, 2009 1:26 PM Marcel wrote: SUMPRODUCT multiple criteria (with a twist) =SUMPRODUCT((D11:D60000=1)*(L11:L60000=<0)*(L11:L6 0000)) take of the "" cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "GD" escreveu: On Tuesday, January 06, 2009 1:33 PM GarysStuden wrote: Don't be concerned. You will learn to love SUMPRODUCT. Don't be concerned. You will learn to love SUMPRODUCT. =SUMPRODUCT((D11:D60000=1)*(L11:L60000<0),(L11:L60 000)) also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html and http://www.mcgimpsey.com/excel/variablerate.html -- Gary''s Student - gsnu200824 "GD" wrote: On Tuesday, January 06, 2009 1:34 PM g wrote: I don't love it yet, but I'm starting to warm up to it. I don't love it yet, but I'm starting to warm up to it. Your solution worked great. I was using the website, but I didn't see any less than/greater than examples. Thanks!! -- GD "T. Valko" wrote: On Tuesday, January 06, 2009 1:41 PM Marcel wrote: Gary,Sumproduct, is fantastic, but when I use it on huge spreadsheets, it Gary, Sumproduct, is fantastic, but when I use it on huge spreadsheets, it loose more time on calculation if compared with other functions or pivot tables, did you have the same? cheers -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gary''s Student" escreveu: On Tuesday, January 06, 2009 3:37 PM ShaneDevenshir wrote: Hi,Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, Hi, Actually SUMPRODUCT is 10%-25% faster than an equivalent array formula, however, pivot tables are faster still. Try the array equivalent: (press Shift+Ctrl+Enter to enter the formula) =SUM((D1:D60000=1)*(L1:L60000<=0),L1:L60000) For one thing the pivot table calculations don't need to convert from Excelese to a low level language. Also, Microsoft has put a lot of time into optimizing the pivot table and that may be harder for a function like SUMPRODUCT. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Marcelo" wrote: On Wednesday, February 03, 2010 5:00 AM noor hussain wrote: Sum Product Function i need you help to use the sumproduct function The below formula is ok in same sheet but wen i try to get the data in another sheet through thisformula it didnt give desired results. =SUMPRODUCT(--(E2:E11=H1),--(A1:A10=G2),F2:F11) Please help me noor On Thursday, March 04, 2010 2:34 PM robin l wrote: sumproduct I am having some issues with sumproduct. I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) On Thursday, March 04, 2010 2:55 PM robin l wrote: sumproduct I am having some issues with sumproduct. I have the formula working. It looks for the region that starts with C* and adds the details - if there are no comments (M is comments and B2 is null). I could not get the null to work any other way. This works though. =SUMPRODUCT((Detail!$A2:$A11="C*")+(Detail!$M2:$M1 1<$B$2),(Detail!$L2:$L11)) The issue is when I expand the rows: When I use the below formula, I get the total of all regions that have no comment. It is ignoring the first criteria. What am I doing wrong? =SUMPRODUCT((Detail!$A2:$A15000="C*")+(Detail!$M2: $M15000<$B$2),(Detail!$L2:$L15000)) Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Circular Progress Indicator http://www.eggheadcafe.com/tutorials...gress-ind.aspx |
| All times are GMT +1. The time now is 08:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com