Sumproduct Question #1

I have been doing a lot of reading on this function and can usually get it to 
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is 
the difference between using * or , to separate the arrays in the argument?

Kevin
0
Kevin105 (377)
3/28/2008 6:40:00 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
399 Views

Similar Articles

[PageSpeed] 43

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The bad news is that Bob's site is experiencing problems.

You may want to try every so often to see if it's back up.

Kevin wrote:
> 
> I have been doing a lot of reading on this function and can usually get it to
> work to do the job - not overly complex.
> One thing that is still not understood (and not the only thing) is what is
> the difference between using * or , to separate the arrays in the argument?
> 
> Kevin

-- 

Dave Peterson
0
petersod (12005)
3/28/2008 9:50:29 PM
Here's a quick exercise to illustrate some of the differences:

Put these values in A1:C5
Name____Amount1__Amount2
Alpha___100______10
Bravo___200______20
Charlie_300______30
Delta___400______40

Now try these formulas:
E1: =SUMPRODUCT(--(A1:A5="Bravo"),B1:B5)......One Amount col
E2: =SUMPRODUCT(--(A1:A5="Bravo"),B1:C5)......Two Amount cols
E3: =SUMPRODUCT((A1:A5="Bravo")*B1:C5)......Incl. heading row
E4: =SUMPRODUCT((A2:A5="Bravo")*B2:C5).....Excl. heading row

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Kevin" <Kevin@discussions.microsoft.com> wrote in message
news:A73C8329-A2EB-45E5-9EAD-A3132243D27A@microsoft.com...
>I have been doing a lot of reading on this function and can usually get it
>to
> work to do the job - not overly complex.
> One thing that is still not understood (and not the only thing) is what is
> the difference between using * or , to separate the arrays in the
> argument?
>
> Kevin




0
3/28/2008 10:04:35 PM
The determining factor as to what form to use should be by how the
calculating data range(s) are to be populated.

The main difference between the unary form and the asterisk form is that
when using the asterisk, *all* the data in the calculating range(s) *must*
be numeric.
That numeric data can even be numeric text - just as long as it looks like a
number, the asterisk form will calculate it.
If it's not in any numeric form, it will return an error.

The unary form will simply by-pass any text or non-numeric data, and
complete the calculation using only *real XL recognized* numbers.

Therefore, if you're populating your calculating ranges with formulas that
may return nulls ( "" ), or text messages (such as "No Data Present"), then
the unary form is the *only* way to go.

However, if data is to be either keyed in, or imported, the asterisk form
*should* be the form of choice, since it will calculate the various forms of
numbers that are usually imported from other apps or the web.
Imported numeric text will by-passed with the unary form, without any
indication as to what data was or was not used in the calculation, which may
produce inaccurate returns.

Another difference between the two forms, is that the asterisk form *must*
be used when *uneven* range sizes are incorporated in calculating 2
dimensional (row v. column) formulas.

With all things being equal, the unary form is supposedly the faster to
calculate, making it the choice for very large ranges.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kevin" <Kevin@discussions.microsoft.com> wrote in message
news:A73C8329-A2EB-45E5-9EAD-A3132243D27A@microsoft.com...
> I have been doing a lot of reading on this function and can usually get it
to
> work to do the job - not overly complex.
> One thing that is still not understood (and not the only thing) is what is
> the difference between using * or , to separate the arrays in the
argument?
>
> Kevin

0
ragdyer1 (4060)
3/29/2008 6:18:48 PM
Thank you for the responses, they were very helpful.
I am certain that Question #2 will be posted as my usage progresses.

Kevin

"Kevin" wrote:

> I have been doing a lot of reading on this function and can usually get it to 
> work to do the job - not overly complex.
> One thing that is still not understood (and not the only thing) is what is 
> the difference between using * or , to separate the arrays in the argument?
> 
> Kevin
0
Kevin105 (377)
3/29/2008 10:12:01 PM
We appreciate your feed-back.
-- 

Regards,

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

"Kevin" <Kevin@discussions.microsoft.com> wrote in message 
news:C0F3A505-484C-41FE-9CD6-B416278BAAC6@microsoft.com...
Thank you for the responses, they were very helpful.
I am certain that Question #2 will be posted as my usage progresses.

Kevin

"Kevin" wrote:

> I have been doing a lot of reading on this function and can usually get it 
> to
> work to do the job - not overly complex.
> One thing that is still not understood (and not the only thing) is what is
> the difference between using * or , to separate the arrays in the 
> argument?
>
> Kevin 


0
ragdyer1 (4060)
3/31/2008 2:45:06 PM
Reply:

Similar Artilces:

Is Version Updated after sp-1 is updated on Money 2007
After you get the automatic software downloaded is it necessary to click onto the sp-1 to install? -- Deb No it is automatic. If I recall, after the SP1 is downloaded, you are prompted to close Money and restart it. After you restart Help > About should give you a version number ending in .1024. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Rebelleheart" <Rebelleheart@discussions.microsoft.com> wrote in messa...

Question about easy qualitative graph
Here's what we want to graph. Categories are snowfall, snowpack, temperature, and precipitation. We want these to be on the y axis. Values are above, normal, below. We want these to be on the x axis with them going from left to right (below, normal, above) with space to the left of "below". Snowfall=above, snowpack=above, temperature=below, precipitation=normal. This sounds very easy, but not sure how to plot this without using numbers, which we aren't using. Thanks for your time, S On Mar 22, 10:19=A0pm, shadrack <shadke...@hotmail.com> wrote: > Here's what ...

ERROR 1053 when strating CRM 1.2 on Windows 2003
When strating the MSCRM Security Service I get the following error. ERROR 1053: THe service did not respond to the strat or control request in a timly fashion. We have just installed CRM prior to migrating it from a 2000 server. Any sygestions on what could be causing this ?? Thanks ...

Updating Money Deluxe 2007 question
Will Microsoft keep the Money updates available permanently, as they do with updates for old operating systems like Windows 98? The reason I ask is that, at least in my experience, Money updates have never been a manually downloadable file, but rather once you install the program, Money calls out on the Internet and looks for updates. If Microsoft doesn't keep the updates online, is there any way to download the update files now for permanent keeping? I did a test. I installed Money 2007 Deluxe on my Virtual PC. As soon as I went into the program after installation, it updated ...

TreeView XML Question
Hi Guys, I have different pages in ASP.NET.And i have a treeview that populates XML that i use as the MENU. Is it possible to have one asp.net page but populating different XML to the mENU? Any ideas? ...

XmlTextReader Question #2
Hi, I am starting out an xml file, example's below. It will be like that but larger. I am having problems actually getting at the inner nodes. Example the items I named InputOne, State, DebounceOn. Not sure how to get each data item Any help, directions would be appreciated. Right now I am doing in C#, SystemIO.StreamReader stream = new SystemIO.StreamReader("theFile.xml); XmlTextReader reader = null; reader = new XmlTextReader(stream); while( reader.Read()) switch(reader.NodeType) { case XmlNodeType.Element: Console.Write( reader.Name + reader.Value); break; case XmlNo...

how to down load more than 1 year statements
HI I like to download more than one year of statment from my brokerage. But it only goes back to 1 year. I want to download statement from 1-1-2003 to 12-31-2003. but since I am downloading in March 2004 it only goes back to march 2003. I had money 2001 it didn't have the option to download more than one year of statement. I got mony 2004 but same thing it only goes back to March 2003. I have noticed that it can arrange reports from 1-1-2003 to 12-31-2003. But still missing the actual data from 1-1- 2003 to March 2003. Please advise how I can achieve that goal. Thanking in anticipa...

array formulas-sumproduct and average
Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

WLK 1.5: IO Cancellation with Direct IO on network storage (Preview)
Hello, I am trying to run unclassified tests for NDIS5 virtual miniport + IM filter driver - we need WHQL signature for Win7. There is new test "IO Cancellation with Direct IO on network storage (Preview)" that keeps me from having submission report to be "likely accepted". When I skip it, Submission reports as "will fail" because test is missing. I didn't found anything about this test in DTM help, nor when I tried to "google" anything. When I select it, it requires additional setup - need to fill local/ remote message/support devic...

Can I have one register from two accounts in M03 and in import question
I have just begun trying-out Money 2003 and am having trouble doing what I want to do. I have two accounts, one is a Merrill Lynch money market account which contains my portfolio and my VISA account and I also write checks in that account. The second account is a Credit Union account in which deposits are made and from which I pay bills. I would like to have one register that will contain all deposits and expenditures, including the VISA charges, from both accounts. Can I do this and how? Also, ML only keeps 90 days worth of transactions. I would like to load the entire year into Mone...

Question about restarting money
It has been over a year since I have updated my money account. I am ready to start again, but don't know the best way to start over. Do I just start a new account or try to update the old one in some way? any advice will be appreciated Grace Unless you want to input a year's worth of transactions the easiest way is to create a new file. File > New -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "Grace" <grace_33322@yahoo.com> wrote ...

Question regarding opening Calender
When I open Outlook it always open to my email. I want to open it to see my calendar always. How might I do that? Thanks! Russ Russ's Signature Page http://home.comcast.net/~rblwood/Signature.html Tools->Options->Other->Advanced. Change it using the dropdown list. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, SignatureMan asked: | When I open Outlook it always open to my e...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

It is a question about the Visual C++ 6.0 Enter(or Pro)
Hello. Environment : Visual C++ 6.0 Enter versions When I add approximately 1,500 sources to one DLL and build. vc60.idb becomes around 35MB, and the following errors are displayed, and build stops. \x1.cpp(19) fatal error C1073: Internal error involving incremental compilation(compiler file 'main.c', line 629) \x1.cpp(23) fatal error C1073: Internal error involving incremental compilation(compiler file 'main.c', line 629) When I delete vc60.idb, An error is not displayed, and build is performed till the last. Thanks, "teratera" <teratera@discussions.m...

IE8 privacy question
I am running XP-Pro SP3 and considering upgrading from IE7 to IE8. I have read all the feature and benefit articles from MS, but have one question that remains unanswered. In IE8 there is mention of being able to restore previously viewed websites or something to that effect. I think this is on a drop down menu somewhere. 1) Does In-private viewing prevent this? 2) Does manually deleting your complete browsing history clear this? 3) Is there a registry entry that can prevent this action? 4) Is there a group policy change that can prevent this action? In essence for privacy pur...

This 1 weird tip works for flattening the belly 65767
www.fantastic-body.com Some of my greatest progress in life has come by listening to my elders. Wisdom often comes with age. Well in this case, fatloss wisdom comes with really, really... REALLY old age. I'm talking Fred Flintstone-age. But once I saw how it works, it made perfect sense to try it myself... .... and what do you know? Flatter stomach; less bodyfat; and better muscle tone. Even sleep improves. All from this 1 tip. There's a presentation you need to see: http://www.everyotherdaydiet.com/go/goodshape <-- Click-Here That will tell y...

focus question
Hi! I am developing an application for specific pda... i need to create a Software Input Panel(normally its implemented in win ce but the oem excluded it from the system)... the problem is as follows: The software keyboard is in a separate child window... no i need to be able to keep the caret on its place(on a selected editbox for example) while pressing buttons on the keyboard. Please help :) Another question is, what message do i have to send to the application in order to put/send a character to an editbox(specified by the caret). Thanx. climax! wrote: > Hi! > > I am developi...

Simplifying my previous question ...
I suspect that (since I didn't get any response on my previous request for assistance) I may need to approach this in stages ... Assuming that Information Rights Management (IRM) won't give me the detailed permissions I need, basically, here's what I need: 1. A Macro that will unprotect a series of Workbooks (approximately 200 of them) so that the Auto Update function in Linked Data can update without user intervention. 2. I copied this Macro from a previous post -- how can I modify the following code to achieve what I need from it: Can I use something like this? I ...

I Made $1.25 million last year alone, I'll show you how!!!
Let me introduce myself. My name is Tony Stevens and for many years I worked for other people on the daily grind. 9 - 5 and much longer hours for what now seems like very poor pay. I suppose looking back on it now, I didn't really know how trapped I was until I discovered an easy way to make money using Ebay and the Internet. Since then I haven't looked back and have been making money consistently. For a long time now, I have been developing a number of interesting methods that can realize lots of money both on and off ebay. I am now offering you the opportunity to do the same. Each me...

Toolbar only shows symbols plus font question
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3348491993_24392315 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit I have 2 questions: When I copy/paste text from another source (another email message or a Word document) into an email message, the font changes to a different typestyle/size which Entourage does not allow me to change. I end up having to retype it all if I want it to look uniform rather than like a ransom note. What do I n...

Autofilter question
I have an autofilter with a column of data that includes operators such as > and <. For example, one of the records would read "> 60 days past due" How would I go about coding the criteria to show the records that display "> 60 days past due"? Thanks, Chad Macro recorder returns this. Selection.AutoFilter Field:=1, Criteria1:="=> 60 days past due" Gord Dibben MS Excel MVP On Mon, 26 Apr 2010 13:23:01 -0700, Chad <Chad@discussions.microsoft.com> wrote: >I have an autofilter with a column of data that include...

Upgrading from Office X Service Pack 1
Hello I purchased Office X (Student and Teacher Edition) today. Applications installed OK, but upgrade to 10.1.2 from Service Pack 1 fails. At the first attempt I get "This updater could not find Office v.X or you have clicked cancel......" I then remove the MS Component Plugin file. The installer finds the directory but returns the message "An error prevented the update from completing. 11002:2,-14" I have tried all the recommended trouble shooting techniques including - reboot my Mac (!), switch virus checker off, shut down all other applications, I've even tried r...

Yet another address book question
Just move to Outlook ought three and t'was my good luck to see a posting here simply saying the you could no longer automatically put people you reply to in your address book. That's what I was looking for. Followed the link to some code to work around this but there was no explanation about how to make the code work. So, what's the real work around for this handy feature (automatically adding people to your phone book)? Do I just use OE or move to some other email program? Thanks, Becky If you are referring to the sample code at http://www.outlookcode.com/d/code/autob...

Question on Budgets for Money 2005
I am considering buying Money 2005. I have a question about the budget feature in Money 2005. I was wondering if you can specify when the budget starts and ends each month. For instance, my budget goes from the middle of the month to the middle of the next month. Thanks for your help. The budget stuff has changed a bit (advanced budget (same as 2004) versus the 'essential' budget), but having budgets running from custom dates is not one of them. -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny ...

Upgrade to 1.3
We are contemplating upgrading to Version 1.3 of MS RMS. We have been told that we cannot have HQ and Store running on different versions whilst they are making connections. So this would mean stopping the connections until HQ and all stores are upgraded. Is this correct? If this is correct, suggestion to MS - make it possible to keep the business running whilst doing an upgrade - e.g. allow upgraded HQ to support connections from Stores that are still to be upgraded? This is not true. I am currently going through the upgrade process. I have 7 stores on 1.3 while HQ is running 1.2. ...