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:$M11<>$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:
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
60000="1")*(L11:L60000="<0")*(L11:L60000))
What am I doing wrong? I've never used SUMPRODUCT before, and I think I
hate it!
--
GD
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
60000=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
RE: SUMPRODUCT multiple criteria (with a twist)
=SUMPRODUCT((D11
60000=1)*(L11:L60000=<0)*(L11:L60000))
take of the ""
cheers
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"GD" escreveu:
Don't be concerned. You will learn to love SUMPRODUCT.
Don't be concerned. You will learn to love SUMPRODUCT.
=SUMPRODUCT((D11
60000=1)*(L11:L60000<0),(L11:L60000))
also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://www.mcgimpsey.com/excel/variablerate.html
--
Gary''s Student - gsnu200824
:
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
:
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:
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
60000=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
:
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
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:$M11<>$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/tutorial...6e-7d3fb7d38eca/join-lists-with-linq--sh.aspx