What wrong with sumproduct function?

I would like to sum all numbers, which match following conditions,

=SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
press ctrl + shift + enter

but it returns zero, and I have checked it, zero should not be the result.
Does anyone have any suggestions what wrong with sumproduct function?
Thanks in advance for any suggestions
Eric
0
Utf
12/8/2009 4:18:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
919 Views

Similar Articles

[PageSpeed] 23

1. Sumproduct is not an array function. Use Enter, not Ctrl-Shift-Enter.
2. You must convert false/trues to numbers. One way:
=SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400))

Regards,
Fred

"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:94EB8EB9-4C16-4705-87C2-5A7D07498067@microsoft.com...
>I would like to sum all numbers, which match following conditions,
>
> =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
> press ctrl + shift + enter
>
> but it returns zero, and I have checked it, zero should not be the result.
> Does anyone have any suggestions what wrong with sumproduct function?
> Thanks in advance for any suggestions
> Eric 

0
Fred
12/8/2009 4:21:30 AM
=SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Eric" wrote:

> I would like to sum all numbers, which match following conditions,
> 
> =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
> press ctrl + shift + enter
> 
> but it returns zero, and I have checked it, zero should not be the result.
> Does anyone have any suggestions what wrong with sumproduct function?
> Thanks in advance for any suggestions
> Eric
0
Utf
12/8/2009 6:58:01 AM
You don't need Control Shift Enter, but you do need to convert the boolean 
TRUE/ FALSE to a number 1/ 0.  The usual way is the double unary minus.
=SUMPRODUCT(--($B$1816:$B$2400=$A2402),--(C$1816:C$2400<0),C$1816:C$2400)
--
David Biddulph

"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:94EB8EB9-4C16-4705-87C2-5A7D07498067@microsoft.com...
>I would like to sum all numbers, which match following conditions,
>
> =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
> press ctrl + shift + enter
>
> but it returns zero, and I have checked it, zero should not be the result.
> Does anyone have any suggestions what wrong with sumproduct function?
> Thanks in advance for any suggestions
> Eric 


0
David
12/8/2009 9:06:57 AM
I thought I'd already replied to this, but I guess that the message didn't 
get out of my outbox.

You don't need Control Shift Enter, but you do need to convert the boolean 
TRUE/FALSE to numbers 1/0/  The usual way of doing so is the double unary 
minus.
=SUMPRODUCT(--($B$1816:$B$2400=$A2402),--(C$1816:C$2400<0),C$1816:C$2400)
--
David Biddulph


"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:94EB8EB9-4C16-4705-87C2-5A7D07498067@microsoft.com...
>I would like to sum all numbers, which match following conditions,
>
> =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400))
> press ctrl + shift + enter
>
> but it returns zero, and I have checked it, zero should not be the result.
> Does anyone have any suggestions what wrong with sumproduct function?
> Thanks in advance for any suggestions
> Eric 


0
David
12/8/2009 10:48:19 AM
Reply:

Similar Artilces:

wrong email address sends
How do I make Outlook send the correct email address and not a business account that I do not want going out with my messages I'm not sure if I understand your question correctly -- are you asking how to force Outlook to send messages through one account rather than another? If so, I need some more information from you before I can answer: What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". ...

Emails shown with wrong date in overview-screen?
hello NG, i have the following problem: i have made a constellation of folders in witch i sort my incoming and outgoing mail to. now i have found the following phaenomenon: in one of these folders i see a bunch of mail (sent by myself) witch all seem to be sent on the same date, 15. december 2004. but in reality this is the wrong date, they have been sent over a long period reaching back even until 2001. if i doubleklick on the mail and view it the correct date is shown. only in the "overview-screen" where all the mails are listed, the date is shown wrong. what can i do about ...

What wrong with sumproduct function?
I would like to sum all numbers, which match following conditions, =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400)) press ctrl + shift + enter but it returns zero, and I have checked it, zero should not be the result. Does anyone have any suggestions what wrong with sumproduct function? Thanks in advance for any suggestions Eric 1. Sumproduct is not an array function. Use Enter, not Ctrl-Shift-Enter. 2. You must convert false/trues to numbers. One way: =SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400)) Regards, Fred "E...

Wrong Date/Wrong amount
In the Money 2004 check register it reports the last download date as well as the balance in the top left corner. The only problem is that it is totally wrong. It reports some bogus amount with a "downloaded" date of 10/9/2003. It is quite stubborn as nothing seems to affect it. Does anyone have any idea of how to correct this? Dirty Harry Harry Calahan wrote: > *In the Money 2004 check register it reports the last download dat > as > well as the balance in the top left corner. The only problem i > that > it is totally wrong. It reports some bogus amount wi...

Help please
I screwed something up and need help big-time. Somehow the default position of the cursor when I start a new document - or Outlook mail message - is in the right center part of the page. In fact, if I even open a document, it's formatted with everything scrunched over to the right. I can backspace and get the cursor to the right and it's not a big problem on documents with simple formatting, but for some complex documents, simply backspacing makes the formatting even worse. HELP PLEASW. -- rpbilleaud For a start, see http://word.mvps.org/FAQs/AppErrors/BlankDo...

How can I use the vlookup function to return a sum of the values?
Hi, I am trying to use the vlookup function to return several values as 1 result into 1 single cell. For example, i have a spreadsheet which lists several different dpeartments. These departments may be repaeted many times in the spreadsheet. I need a sum of thoses values to be returned in a separate cell using the vlookup function. Can someone help me on this please? Thanks Chaandni Wrote: > Hi, > > I am trying to use the vlookup function to return several values as 1 > result > into 1 single cell. For example, i have a spreadsheet which lists > several > differ...

If function limitations help
I am created an advanced gradebook program for excel and was wondering if anyone had an idea of a better way to accomplish the following Column2 Column2 a 3 b 2 c 5 d 10 etc... with the output being in one row a a a b b c c c c c d d d d d d d d d d The idea is to have the teacher type the subject and how many times it appears as a heading but after the 4th I have to many if functions nested. I am using: if b2>=1,a2 for the first return and =IF(B2>=2,A2,IF(B2=1,A3)) for the second, =IF(B2>=3,A2,IF(B2=2,A3,...

How to create a function in a code sheet
Hi! I need to create a function that I will use in a worksheet. However, I need to create it in a worksheet code (not in a separate module, but in the "Microsoft Excel Objects"). I know it works for a macro, but not for a function. I tried to call my function with "private function", "public function", but it doesn't work. Thanks for your help guys! Mathieu Hi Mathieu Why on earth do you "need to create it in a worksheet code, not in a separate module" ? Insert a regular module and write your function, end users don't ca...

What's going wrong in Outlook 2003?
Hello, When I installed the new Office 2003, I had no problems with Outlook 2003. I received and send mails whenever I want. A few days later, I get a message from outlook that Outlook could not make a connection with the server of my provider to get my mail on my computerb (I didn't change any settings!). I checked all my settings and there are good (I checked the mailserversettings, ports, etc) but the result is still the same: Outlook cannot make a connection with the mailserver. After that, I called my provider to ask them what to do. They told me that I must remove all the ...

wrong number in cell
In XP PRO & Excel 2002 I have a cell formated as General and unlocked. I try to enter a number { 08607100 } I hit enter and it changes to { 8607100 } no matter what I try it. This is another example of a problem in a cell I enter { 03E2 } hit enter and it changes to { 3.00E+02 }. Is there some bug in certain cells or with excel 2002 or is there some setting I am missing? Help -- Thanks MK pipco wrote: > In XP PRO & Excel 2002 I have a cell formated as General and unlocked. I > try to enter a number { 08607100 } I hit enter and it changes to { 8607100 &g...

What Am I Doing Wrong?
The application I'm modifying is a fairly standard example of an MFC MDI app. There are several CToolBars created in MainFrm.cpp. The code to respond to the buttons on these toolbars is in the View.cpp file. One of these toolbars I modified to contain a slider. I derived a new class, CViewToolBar, from CToolBar and modified it to have a slider. I'm having problems with two major things: the correct way to include the new class information so that MainFrm knows about it to create the new toolbar, and how to get the view to respond to WM_HSCROLL messages from the slider. I had ...

Error "undefined function 'left' in expression"
Hi everybody, Has there anyone got a solution to my problem: - recently got Windows Vista - since then, queries with the string function left (e.g. left("member",2) equals "me") don't work Further details: - Windows Vista Home Premium - Regional settings: Dutch - Office (Access) version 2003 SP2 Any help will be greatly appreciated. Thkx. M.S.Huizenga Reference problem. Go to the following web page for information on checking references: http://www.mvps.org/access/bugs/bugs0001.htm -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith ...

Using functions
How can I use these user defined functions so that they are available whenever I open a new workbook? You need to put them in your Personal.xls file. The Personal.xls file resides in your XLStart folder and is opened whenever Excel opens. You may or may not have such a file. To find out if you already have one, open Excel. Then click on Window - Unhide. If Personal.xls is listed to unhide then you have such a file. If it is not listed or if the "Unhide" word is grayed-out, then you don't have one. To create it, do this: With Excel open, click on Tools - Macro ...

SUM PRODUCT Function
Hiya Sorry about earlier message, sent it before completing it. I have the following spreadsheet: Partner Orpington FRICS Partner Orpington FRICS Partner Orpington BSc FRICS I Eng AMI Struct E Partner Orpington FRICS Partner Orpington BA(Hons) MRICS Partner Orpington MRICS BSc Partner Orpington BSc Dip Arch(Hons) RIBA Partner Orpington ACIOB MAPM Partner Orpington BSc MRICS Partner/ Site Assessor Orpington BSc FRICS I Eng AMI Struct E Partner Associate Orpington BSc FRICS I Eng AMI Struct E Partner Orpington BSc MRICS Partner Orpington MRICS Partner Orping...

run a function within a script with different credentials
I have a powershell script that is launching a process on a remote machine. I would like to have it so the script prompts for the persons username and password (get-credential) and then takes those credentials and uses them to launch the process. The script works find if I launch powershell with the credentials that need to launch the process on the remote machine, but if I launch the powershell script with credentials that don't have access to that machine it fails. Could somebody provide an example of this? On 12/18/2009 6:46 PM, RG wrote: > I have a powershell scrip...

Show/Hide Functions/Formulas
For some reason the keyboard shortcut (Ctrl+`) does not toggle show/hide function. Selecting Tools>Formula Auditing>Formula Auditing mode works fine. I am wondering if it is because I have regional (keyboard) settings set to UK English Extended. I know the `(grave) key works and have tried it with both Ctrl keys with no luck. Help please. "Smirnoff" <someone@nospam.invalid> wrote in message news:73972258-4ADC-4E40-B17B-A4E64E2B4B7F@microsoft.com... > For some reason the keyboard shortcut (Ctrl+`) does not toggle > show/hide function. > > Selecti...

Recurring split bills download wrong!
Money 2004: I have recurring bills with estimated amounts and splits. When I change the amount/splits and submit for payment, the check is being handled just fine through MSN Bill Pay. When I download the consummated transaction from Wells Fargo, however, Money is pulling in my ESTIMATED splits instead of my ACTUAL splits for that payment. Hence the splits never match the actual payment amount, and I get a warning. What a pain. This problem started when I switched from Wells Fargo bill pay to MSN. As I see it, though, it's Money's job to keep track of the splits internally, t...

How can I use a non-static dll function in a worker thread
Hello all, I want to call a W32 syle driver function for a USB device (FTDIchip) in my worker thread. The function is ecapsulated in the driver dll. The driver provides functions simular to ReadFile and WaitCommEvent in Win32. So I have implemented in the same way as my woker thread which I use for serial communication. The problem occures on compilation. Error “2352 – illegal call of non-static function” How can I declare the dll function to be static? Is there any way to do this? Tank you for any help or suggestion! Best regards from sunny south Wales! :-) Thomas PS: Here the rel...

undefined function replace in expression
Helloi've read all the threads dealing with the subject.I've written my wrapper to the native replace function.it works fineinside access.i've changed sandbox value to 0.but the problem still persists.in addiction now there is no REPLACE function, but MyReplace.this is the access codeOption Compare DatabasePublic Function MyReplace(StringToSearchIn As String, _ StringToFind As String, _ ReplaceString As String, _ Optional StartHere As Integer = 1, _ Optional HowMany As Integer = -1, _ Optional CompareMode As VbCompareMethod = vbTextCompare) As String MyReplace = R...

Excel functions
Hi everyone, Hope somebody could help me with these problems. Problem #1. row 1 =DSUM(database,field,'$A4:$A5) where A4 = "destin"; A5 = "Consolidator 1" row 2 =DSUM(database,field,'$A6:$A7) where A6 = "destin"; A7 = "Consolidator 2" ..... row n =DSUM(database,field,'$An:$Am) where An = "destin"; Am = "Consolidator n" I waste a lot of rows for this criteria only because I do not know ho to create it another way, I mean (A4, A6 and so on) Problem #2 I have named several range of cells in workbook. I want to change...

INDIRECT function #3
Hi group. Where could I find some good, detailed explanation of the uses of the Indirect function? I find the online help absolutely useless on this particular topic. I don't even understand why and when this function could be useful. Hi one usage: - say you have some sheets which are name like a month name (e.g. January, February, etc.) - on a different sheet you want to show the value of cell X1 from one of these month sheets. But you don't want to use a formula like ='January'!X1 and change the formula manually if you want a different month. Instead you want to insert...

"Find Most Opposite" Function?
Is there a way to search for a row that is "most opposite" to another row? For instance, if the values in the "initial row" were 2 in column A, 2 in column B, and -2 in column C, I'd want to find the "most opposite" row where column A might read -2, column B might read -1, and column C might read 2 (as opposed to a "similar" row where column A might read 2, column B 1, and column C -1). Thanks for any advice :) You don't really define what constitutes the what makes one row different from another and how such a difference is to b...

User Function Update upon opening
I've created a function module with the following function:- Function User() User = Application.UserName EndSub Cell A1 now has a formula " =User()" This returns the User name to the cell as required. Ie "Joe Bloggs" Now if "Joe Bloggs" saves the workbook and another user "Jim Smith" opens the workbook "Joe Bloggs" still appears in Cell A1. Deleting the contents of Cell A1 and re-entering the function "=User()" will now return "Jim Smith" as the user as required. So my question is how to get excel to update ...

Contact data is in wrong field
Hi all - I need some help with a complicated problem. I am working on a PST file that was recovered from some old data. Unfortunately, the recovery put contact data in the wrong places. Here are a few examples: 1. Contact street address and city shows up in the "Follup up flag" field 2. Contact "Email display as" contains the display as information with the email address, but the email address field is empty 3. Many duplicates of data with slightly different information (Same contact might have 3 copies, one with a missing telephone number, another with a missing emai...

transactions to read (wrong #)
I have two accounts (one is my bill pay account) that are reporting transactions to read even though all the transactions are accepted. Any ideas? -- Mike In microsoft.public.money, Mike Brearley wrote: >I have two accounts (one is my bill pay account) that are reporting >transactions to read even though all the transactions are accepted. Any >ideas? Close Money In Internet Explorer delete your browser temporary files with Tools->InternetOptions->DeleteFiles. Then open Money with Start->Run and entering "msmoney -s" without the quotes into the box....