 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008"SUMPRODUCTIF"
I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. Try something like this: =SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I find myself asking a lot of questions on these boards lately... Thank you > to all you great people that make the time to help! [quoted text clipped - 11 lines] > > ... I would want to return [(2*6) + (4*12)] = 60. That seems to be doing exactly what I needed... What do the two minus signs (--) preceding that argument do? I did a little searching around, and it looks like they... multiply by 1... thereby doing something with the format? I'm still lost! > Try something like this: > [quoted text clipped - 25 lines] > > > > ... I would want to return [(2*6) + (4*12)] = 60. In this formula =SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) (A1:A10="YELLOW") returns a series of TRUE/FALSE values which are NOT numeric, so SUMPRODUCT can't use them "as is" However, in Excel, when TRUE/FALSE values are acted upon by an arithmetic operator (+, -, *, / ) it converts TRUE to 1 and FALSE to 0 We use the Dbl-Minus as an unambiguous way of indicating that we are forcing a numeric conversion. It works this way: TRUE = TRUE -TRUE = -1 --TRUE = 1 So this series: --(TRUE; FALSE; TRUE; TRUE) becomes 1; 0; 1; 1 Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > That seems to be doing exactly what I needed... What do the two minus > signs [quoted text clipped - 35 lines] >> > >> > ... I would want to return [(2*6) + (4*12)] = 60. The first "-" coerces Excel to change the logical value of TRUE to its numeric equivalent of 1. But the "-", makes the TRUE -1. The second "-" makes the -1 a 1. Simple algebra, -(-1) = 1. As for the logical value FALSE which is 0, the application of "--" has no meaning since 0 is neither positive or negative and remains 0 In Excel a 0 is FALSE and all other values are TRUE. Tyro > That seems to be doing exactly what I needed... What do the two minus > signs [quoted text clipped - 35 lines] >> > >> > ... I would want to return [(2*6) + (4*12)] = 60. =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) Tyro >I find myself asking a lot of questions on these boards lately... Thank you > to all you great people that make the time to help! [quoted text clipped - 11 lines] > > ... I would want to return [(2*6) + (4*12)] = 60. Hey, that works too! Thanks, Tyro. > =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) > [quoted text clipped - 15 lines] > > > > ... I would want to return [(2*6) + (4*12)] = 60. Simple matter of coersion, "--" or "*" in this case. Tyro > Hey, that works too! Thanks, Tyro. > [quoted text clipped - 20 lines] >> > >> > ... I would want to return [(2*6) + (4*12)] = 60. You've both explained it very well... I see that the -- is simply allowing the value of the next statement to act as an "eliminator" of sorts so I only get multiplication through on the rows that I wanted. I can already think of other places where I can implement that trick. Thanks again. > Simple matter of coersion, "--" or "*" in this case. > [quoted text clipped - 24 lines] > >> > > >> > ... I would want to return [(2*6) + (4*12)] = 60. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > You've both explained it very well... I see that the -- is simply allowing > the value of the next statement to act as an "eliminator" of sorts so I [quoted text clipped - 34 lines] >> >> > >> >> > ... I would want to return [(2*6) + (4*12)] = 60. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.