SUMPRODUCT Qusetion

  • Thread starter Thread starter QuietMan
  • Start date Start date
Q

QuietMan

What is the difference between these two formulas?
sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*(SumRange))
sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange))

What exactly "--" represent?

Thanks
 
Yes there is. Just trying entering this data

A1:A10 (RangeA): Name,Bill,Bill,Bill,Joe,Joe,Joe,Mark,Jim,Jack
B1:B10 (RangeB): Year,2008,2009,2008,2007,2008,2009,2007,2007,2007
C1:C10 (SumRange): Amt,1,2,3,4,5,6,7,8,9

Run this formula,

=SUMPRODUCT(--(RangeA="Bill"),--(RangeB=2008),SumRange)

and you get 4 as you would expect. But try

=SUMPRODUCT((RangeA="Bill")*(RangeB=2008)*(SumRange))

and see wat you get.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Yes there is. Just trying entering this data
A1:A10 (RangeA): Name,Bill,Bill,Bill,Joe,Joe,Joe,Mark,Jim,Jack
B1:B10 (RangeB): Year,2008,2009,2008,2007,2008,2009,2007,2007,2007
C1:C10 (SumRange): Amt,1,2,3,4,5,6,7,8,9

Run this formula,

=SUMPRODUCT(--(RangeA="Bill"),--(RangeB=2008),SumRange)

and you get 4 as you would expect. But try

=SUMPRODUCT((RangeA="Bill")*(RangeB=2008)*(SumRange))

and see wat you get.
Click to expand...

I get 4 for both formulas.

Rick
 
OR, try entering this data:

RangeA
A2:A4 = Tom, Dick, Harry

RangeB
B1:D1 = 2006, 2007, 2008

Sum Range
B2:D4 = 1,2,3:4,5,6:7,8,9

And try this:
=SUMPRODUCT((A2:A4="Dick")*(B1:D1=2008)*B2:D4)
And you'll get 8.

But run:
=SUMPRODUCT(--(A2:A4="Dick"),--(B1:D1=2008),B2:D4)
And see what you'll get.
 
I don't think so Rick ... not if you put the TEXT value "Amt" in C1,
and included C1 in the Sum Range, C1 to C10.<g>

That was the point Bob was making.

To make the asterisk form work, you'd have to revise the ranges to *exclude*
the header row:

=SUMPRODUCT((A2:A10="Bill")*(B2:B10=2008)*C2:C10)

My example goes in the other direction, where the unary doesn't work,
and the asterisk form does.
 
Of course, that was not what I did :-(

Thanks for the "kick in the pants" on this.

Rick
 
It's like the Chuckle Brothers <vbg>

RagDyer said:
OR, try entering this data:

RangeA
A2:A4 = Tom, Dick, Harry

RangeB
B1:D1 = 2006, 2007, 2008

Sum Range
B2:D4 = 1,2,3:4,5,6:7,8,9

And try this:
=SUMPRODUCT((A2:A4="Dick")*(B1:D1=2008)*B2:D4)
And you'll get 8.

But run:
=SUMPRODUCT(--(A2:A4="Dick"),--(B1:D1=2008),B2:D4)
And see what you'll get.
Click to expand...
 
What are you doing up so late?

Is it a holiday in GB?<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Bob Phillips said:
It's like the Chuckle Brothers <vbg>

RagDyer said:
OR, try entering this data:

RangeA
A2:A4 = Tom, Dick, Harry

RangeB
B1:D1 = 2006, 2007, 2008

Sum Range
B2:D4 = 1,2,3:4,5,6:7,8,9

And try this:
=SUMPRODUCT((A2:A4="Dick")*(B1:D1=2008)*B2:D4)
And you'll get 8.

But run:
=SUMPRODUCT(--(A2:A4="Dick"),--(B1:D1=2008),B2:D4)
And see what you'll get.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


Bob Phillips said:
Yes there is. Just trying entering this data

A1:A10 (RangeA): Name,Bill,Bill,Bill,Joe,Joe,Joe,Mark,Jim,Jack
B1:B10 (RangeB): Year,2008,2009,2008,2007,2008,2009,2007,2007,2007
C1:C10 (SumRange): Amt,1,2,3,4,5,6,7,8,9

Run this formula,

=SUMPRODUCT(--(RangeA="Bill"),--(RangeB=2008),SumRange)

and you get 4 as you would expect. But try

=SUMPRODUCT((RangeA="Bill")*(RangeB=2008)*(SumRange))

and see wat you get.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
No difference.

Google for:
"double unary minus" Excel sumproduct
--
David Biddulph

What is the difference between these two formulas?
sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*(SumRange))

sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange))

What exactly "--" represent?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Missed your post (went to bed <g>). I was working on an addin during the
evening, and my laptop just shutdown and I lost the lot, so I was redoing it
whilst it was all still in my head.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



RagDyer said:
What are you doing up so late?

Is it a holiday in GB?<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Bob Phillips said:
It's like the Chuckle Brothers <vbg>

RagDyer said:
OR, try entering this data:

RangeA
A2:A4 = Tom, Dick, Harry

RangeB
B1:D1 = 2006, 2007, 2008

Sum Range
B2:D4 = 1,2,3:4,5,6:7,8,9

And try this:
=SUMPRODUCT((A2:A4="Dick")*(B1:D1=2008)*B2:D4)
And you'll get 8.

But run:
=SUMPRODUCT(--(A2:A4="Dick"),--(B1:D1=2008),B2:D4)
And see what you'll get.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------


Yes there is. Just trying entering this data

A1:A10 (RangeA): Name,Bill,Bill,Bill,Joe,Joe,Joe,Mark,Jim,Jack
B1:B10 (RangeB): Year,2008,2009,2008,2007,2008,2009,2007,2007,2007
C1:C10 (SumRange): Amt,1,2,3,4,5,6,7,8,9

Run this formula,

=SUMPRODUCT(--(RangeA="Bill"),--(RangeB=2008),SumRange)

and you get 4 as you would expect. But try

=SUMPRODUCT((RangeA="Bill")*(RangeB=2008)*(SumRange))

and see wat you get.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
No difference.

Google for:
"double unary minus" Excel sumproduct
--
David Biddulph

What is the difference between these two formulas?
sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*(SumRange))

sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange))

What exactly "--" represent?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top