|
SUMPRODUCT Qusetion
What is the difference between these two formulas?
sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
No difference.
Google for: "double unary minus" Excel sumproduct -- David Biddulph "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
See these:
http://xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
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)*(SumRang e)) 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 "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
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)*(SumRang e)) and see wat you get. I get 4 for both formulas. Rick |
SUMPRODUCT Qusetion
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" wrote in message ... 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)*(SumRang e)) 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 "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... 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)*(SumRang e)) and see wat you get. I get 4 for both formulas. Rick |
SUMPRODUCT Qusetion
Of course, that was not what I did :-(
Thanks for the "kick in the pants" on this. Rick "RagDyer" wrote in message ... 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rick Rothstein (MVP - VB)" wrote in message ... 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)*(SumRang e)) and see wat you get. I get 4 for both formulas. Rick |
SUMPRODUCT Qusetion
It's like the Chuckle Brothers <vbg
"RagDyer" wrote in message ... 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" wrote in message ... 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)*(SumRang e)) 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 "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
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" wrote in message ... It's like the Chuckle Brothers <vbg "RagDyer" wrote in message ... 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" wrote in message ... 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)*(SumRang e)) 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 "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
SUMPRODUCT Qusetion
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" wrote in message ... 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" wrote in message ... It's like the Chuckle Brothers <vbg "RagDyer" wrote in message ... 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" wrote in message ... 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)*(SumRang e)) 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 "QuietMan" wrote in message ... What is the difference between these two formulas? sumproduct((RangeA=CriteriaA)*(rangeB=CriteriaB)*( SumRange)) sumproduct(--(RangeA=CriteriaA),--(rangeB=CriteriaB),--(SumRange)) What exactly "--" represent? Thanks -- Helping Is always a good thing |
| All times are GMT +1. The time now is 08:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com