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

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

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

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

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

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

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

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

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

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

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

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

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