sumproduct problem

hello,

i've been reading up on all the sumproduct related posts, but can't figure 
out what's wrong with mine...

this is my formula:
=SUMPRODUCT(Détail!B2:B151=1;Détail!AB2:AB151=1;Détail!T2:T151)

when i look at the insert function arguments box to help define the arrays, 
excel returns the right values, i.e.:
for array 1 : false, true, false, false,.. (this is the first condition)
for array 2 : true, true, true, true,... (this is the second condition)
for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up)

so i thought excel would return '2', i.e. for the second record in each array:
true * true * 2 = 2
however, the value returned = 0

i also tried using the following
=SUMPRODUCT(Détail!B2:B151=1*Détail!AB2:AB151=1;Détail!T2:T151)
but the result is the same.

thanks for your help.
andy
0
Andy1200 (318)
3/29/2006 12:26:01 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
449 Views

Similar Articles

[PageSpeed] 59

SUMPRODUCT requires numeric arguments, while your first two are boolean 
(TRUE/FALSE). Try:

  =SUMPRODUCT(--(Détail!B2:B151=1);--(Détail!AB2:AB151=1);Détail!T2:T151)

See

    http://www.mcgimpsey.com/excel/doubleneg.html

for a more extensive explanation.


In article <DB32ED5F-6006-4461-83B5-74D7869ADEC3@microsoft.com>,
 andy <andy@discussions.microsoft.com> wrote:

> hello,
> 
> i've been reading up on all the sumproduct related posts, but can't figure 
> out what's wrong with mine...
> 
> this is my formula:
> =SUMPRODUCT(Détail!B2:B151=1;Détail!AB2:AB151=1;Détail!T2:T151)
> 
> when i look at the insert function arguments box to help define the arrays, 
> excel returns the right values, i.e.:
> for array 1 : false, true, false, false,.. (this is the first condition)
> for array 2 : true, true, true, true,... (this is the second condition)
> for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up)
> 
> so i thought excel would return '2', i.e. for the second record in each array:
> true * true * 2 = 2
> however, the value returned = 0
> 
> i also tried using the following
> =SUMPRODUCT(Détail!B2:B151=1*Détail!AB2:AB151=1;Détail!T2:T151)
> but the result is the same.
> 
> thanks for your help.
> andy
0
jemcgimpsey (6723)
3/29/2006 12:34:55 PM
many thanks!
problem solved...

"JE McGimpsey" wrote:

> SUMPRODUCT requires numeric arguments, while your first two are boolean 
> (TRUE/FALSE). Try:
> 
>   =SUMPRODUCT(--(Détail!B2:B151=1);--(Détail!AB2:AB151=1);Détail!T2:T151)
> 
> See
> 
>     http://www.mcgimpsey.com/excel/doubleneg.html
> 
> for a more extensive explanation.
> 
> 
> In article <DB32ED5F-6006-4461-83B5-74D7869ADEC3@microsoft.com>,
>  andy <andy@discussions.microsoft.com> wrote:
> 
> > hello,
> > 
> > i've been reading up on all the sumproduct related posts, but can't figure 
> > out what's wrong with mine...
> > 
> > this is my formula:
> > =SUMPRODUCT(Détail!B2:B151=1;Détail!AB2:AB151=1;Détail!T2:T151)
> > 
> > when i look at the insert function arguments box to help define the arrays, 
> > excel returns the right values, i.e.:
> > for array 1 : false, true, false, false,.. (this is the first condition)
> > for array 2 : true, true, true, true,... (this is the second condition)
> > for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up)
> > 
> > so i thought excel would return '2', i.e. for the second record in each array:
> > true * true * 2 = 2
> > however, the value returned = 0
> > 
> > i also tried using the following
> > =SUMPRODUCT(Détail!B2:B151=1*Détail!AB2:AB151=1;Détail!T2:T151)
> > but the result is the same.
> > 
> > thanks for your help.
> > andy
> 
0
Andy1200 (318)
3/29/2006 12:52:01 PM
Reply:

Similar Artilces:

Exchange 5.5 Restore problem #2
We are trying to restore an Exchange 5.5 server and keep getting the same information store error 3355443752. The only tech notes on this say don't run isinteg -patch (Article 231299) but we have not. We have tried multiple restores and 3 different tapes and still get the same error. We have done many older restores and some recent ones as well with no problems. so you did not run isinteg -patch before trying to start the IS? when it starting the IS fails, are you getting the error telling you to run sinteg -patch? is this an online backup you're restoring, or offline? -- Su...

Problem Converting HTML to Access
I have Access 97... When I link an HTML document to my Access table, the list shows that the information is there, but the cells are blank. The names show up only when I highlight a cell. Ex. I have 300 names and the list will say 1 out of 300, but no names will appear in the cell. It is just blank. Any suggesstions as to why this is happening? My first thought is that the HTML code in each cell of the table starts with something (e.g. a <BR> tag) that Access is interpreting as a linebreak or empty line. "rblivewire" <rblivewire@hotmail.com> wrote in message news:11...

Installing Office 07 on Vista (Problem Encountered)
My previous version of office corrupted and won't run, now when I try to install a new version to the same location it fails. I cannot uninstall the older version because it is corrupt - I was wondering if there is a walk-through for a manual uninstall or some support I don't have to pay $40 for. "Karl Neumann" <Karl Neumann@discussions.microsoft.com> wrote in message news:E653F052-BEA5-419D-AF5C-047A2D6FFF21@microsoft.com... > My previous version of office corrupted and won't run, now when I try to > install a new version to the same location i...

Problems saving HKLM
Hi there, I am trying to save the reg keys into a file: HKEY_CLASSES_ROOT HKEY_CURRENT_CONFIG HKEY_CURRENT_USER HKEY_LOCAL_MACHINE HKEY_USERS My program has both "SeBackupPrivilege" and "SeRestorePrivilege". I am using the RegSaveKey API. The program succesfully stores these: HKEY_CLASSES_ROOT HKEY_CURRENT_CONFIG HKEY_CURRENT_USER The machine does not store these: HKEY_LOCAL_MACHINE HKEY_USERS Error ("Access is denied"). Can someone tell me how to save the HKLM amd HKU? This probably what the problem is: http://msdn.microsoft.com/library/default.asp?ur...

EUREKA -- the mutual fund problem is fixed
I just tried once more to update my Fidelity Funds in MS Money and, this time, the update worked. Both the quotes and the date were correct. EUREKA! Honestly, thanks to all who, like me, bugged Microsoft endlessly to understand the complexities of this problem and to get it fixed. We did it! "Chuck Leven" <CHLeven@hotmail.com> wrote in message news:%230lQmZ03FHA.3636@TK2MSFTNGP09.phx.gbl... >I just tried once more to update my Fidelity Funds in MS Money and, this >time, the update worked. Both the quotes and the date were correct. >EUREKA! Mine just upda...

Signature problem #4
I have have Outlook 2000. I�ve had one account only. Now that I�ve added more has created a proble. If I reply to message connected to account A I get signature B. If correct that (having reply open) in options > general email settings etc and change "Show new page with this signature" to A then next time I replay to account B message I get A signature? Is there a way to assigne a signature spesifically to a account? Thanks Kalevi I think that feature was added in later versions of Outlook "Kalevi" <kale@woodworld.fi> wrote in message news:fe2blo$1jj$1@...

DC failure causes mail problems
Environment looks like this: Windows 2000 Active Directory, Native Mode Single AD Site. 2 DCs; DC1 and DC2 DC1 is W2K Server SP4, holds all five FSMO roles and provides DHCP and WINS services. DC1 is also a GC. DC2 is W2K Server SP4, runs Exchange 2000 SP3 and is a GC. DNS1 is W2K Server SP4 and is our primary DNS server to support AD. We have split-brain DNS. Exchange 2000 Native Mode 2 Exchange 2000 servers; DC2 and MAIL1 MAIL1 is W2K Server SP4, running Exchange 2000 SP3 and is a secondary DNS server. Upon arrival at work yesterday, observed that one of our two DCs (DC1) was powere...

Problems XSLT Transformation of XML Data to EXCEL 2000
Does Office Excel 2000 support XSLT transformation of data from XML cause am having problem when the attachment is opened in the client side if the Excels version is in Office 2000 and also its works fine if the Office Excel Version 2002 and up when attachment file is open via browser the data is in a single line of string in the first row of the excel worksheet Glenn Gomez wrote: > Does Office Excel 2000 support XSLT transformation of data from XML cause am > having problem when the attachment is opened in the client side if the Excels > version is in Office 2000 > >...

Queue message problem
Hello, We have a Ex2k/Win2k advanced member server that had some serious problems starting Sat. morning. SMTP, POP, IMAP, www, MS Route Engine all stopped and seemed to start back up and then stop again at various intervals, eventually just stopping completely rendering the mail down. A reboot had the same problems occur. When I removed the messages that were in the queue(c:\exchsrvr\mailroot\vsi 1\queue) and then manually started the services all was well. I would like to copy back some of those messages into the queue to see if they process or cause the same problem again. Once I copy ...

Problem registering Office:mac 2008
I receive the following message when trying to register my new "office:mac home and student":<br> "Id. del producto no v�lido (PID): Escriba un PID v�lido.<br> (Ejemplo: 51872-442-2230403-56078)"<br> <br> My macbook has as preferred language spanish, then a screen on Spain appears when I try to register my product, bought in FL, USA.<br> <br> How can I passthru this message? <aleczir@officeformac.com> wrote: > I receive the following message when trying to register my new "office:mac > home and student":<br> ...

Countif problems
I am currently trying to write a formula that looks up a centre eg Boston and then, from another column, how many 'Bostons' appear between two given dates. Is this possible? Although I am writing this formula other people will have to use it and change the date ranges regularly so it can't be overly complicated. -- Drew C With data arranged as below try the below formula. Query dates are in cells C1 & D1 =SUMPRODUCT((A1:A7>=C1)*(A1:A7<=D1)*(B1:B7="Boston")) Col A Col B Col C Col D 1/25/2010 Boston 1/25/2010 1/28/2010 1/26/2010 NY 1/27/...

Problem with .Values
Hi all, I have a Sub for construct graphic but if the number of vx is too large, ERROR at .Values = Y. Why ? Thank you for your help, Serge Sub ConstruireGraphiqueParTableauxVBA() Dim X() As Double Dim Y() As Double, i As Long, vx As Double ReDim X(i), Y(i) Application.ScreenUpdating = False ici = ActiveSheet.Name '********************************************* For vx = -15 To 15 'If the number of vx is too large, ERROR 'at .Values = Y. Why ? '********************************************* X(i) = vx Y(i) = vx ^ 2 i = i + 1 ReDim Preserve X(i), Y(i) Next vx Cha...

problem with deleting items via OWA on Vista
Good afternoon! We are working on a big project and I have heard from my colleagues that a couple of users are having problems when attempting to delete e-mails when using OWA. Now, unfortunately this is all I know. The colleague who has heard directly from the client is not available for the next several hours. Here is what I do know: Server-side: Exchange Server 2003 SP2 fully patched - did this myself Windows Server 2003 R2 fully patched Two different OUs (CORPORATE Users and KIOSK Users) - did this myself Two different Mailbox Stores (Corporate and KIOSK) - did this myself Differ...

Problem Sending Mail w/Outlook 2002
I receive and send a lot of email using Outlook 2002 and my Gmail account with no problem. However, I have two friends to whom I send emails that are always returned. I get an error message like this: Technical details of permanent failure: PERM_FAILURE: SMTP Error (state 9): 550 Invalid recipient: However, if I send the emails to them directly from Gmail, they go through just fine. Can anyone explain why they fail with Outlook? Pat This sounds like an issue with the SMTP server you have setup in outlook - not outlook itself. "patetc" <PatETC@gmail.com> wrote in messag...

Problem of ghost unread posts?
Hi All! I'm using Outlook Express. Three times today I'm seeing unread posts reported that can't be found. Synchronizing doesn't cure the problem. When I use Edit > Mark All Read I get an OE hang up. Ctrl + Alt + Del > End Task Return to News and the ghost has gone. Anyone else? Cause? Solution? Not critical but annoying. I wonder if it's a remains of a spam that's been removed before I read it. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available fr...

User form problem
I have made a userform, which works great. However i have a problem. I would like the userform to only load up when the template file i opened. I do not want it to load when saved workbook copies of th template are opened. The system I am creating is an invoice system, and thus the loading o the userform is only necessary upon opening the template, not any othe files. Is this possible to do? Any help would be appreciated. Thanks. Z -- Message posted from http://www.ExcelForum.com Hi Zairn You can check the Len of the Thisworkbook.path in the Macro. A template have no path Sub test() I...

Software Distribution
Hi, I try to install software using msi with mst. Software supplier told that I must run msi two times. First for some options and secound for another ( language option ). I try solve this with two way: 1) Add two mst to one package. I prepare mst with ORCA. After add secound mst and click OK i got error: "An error occurred accessing the software installation data in the active directory" and event log: Event id 110: Software Installation was unable to generate the script for \\dc\client.msi. The following error was encountered: Error applying transforms. Veri...

Exchange 2003 / IIS 6.0 WMI Install problem
Some, about half of our fresh installed Windows 2003/Exchange 2003 servers are missing WMI performance classes for monitoring. The only class we know of that does not appear to be installed is: Win32_PerfRawData_SMTPSVC_SMTPServer Contains many sub functions and events that can be monitored. Is there a way to copy the WMI class from one machine that has them to another? We have tried reinstalling SMTP and Exchange 2003, but may have failed because the WMI service kept starting up on its own during the reinstall process, are there any other services or applications we can uninsta...

Multiple Outlook 2003 problems
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3EAFE.732D7EA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 1) every time it starts up I get a runtime error (one of the error = processing line number 67 errors) - It's a default install, nothing = special connecting to an Exchange server. 2) I get prompts every few minutes to login. Same two prompts every = time. One is a windows login prompt (to what I don't know, can only = assume something in outlook) and the other is a .net login. I always = just c...

Problem closing Outlook 2000
Hi all, I have some problem closing Outlook 2000, when i open Outlook there is no problem but once i want to close it hang. Eveytime I need to go to Task Manager to end task. So have anyone have the solution on this. Cause Microsoft Web don't have this solution and if we need help from Microsoft i need to pay S$17.00. Please help. Thanks. Regards See if one of the following already helps you; http://www.howto-outlook.com/faq/outlookdoesntclose.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 ...

Multiplication problem- Please Help!
Hello all. I am a novice Excel user, and cannot seem to locate the source of my problem. Please help! Here is my sheet: C4 D4 E4 F4 G4 H4 9/22/2005 7:10 PM___10/1/2005 12:13 PM___ 8.7__52.6__6.0__317.64 Start date is entered into C4. End date is entered into D4. The following formula is in E4 to determine elapsed days: =IF(D4=0,"",IF(C4=0,"",(IF(ISERROR((D4-C4)*24),"",(D4-C4)*24)/24))) F4 contains "FLAGED HOURS" (for a body shop estimate) G4 contains a value u...

Problem running MS CRM 3.0 over SSL (https)
I have setup an install of Microsoft CRM 3.0 on a high spec Windows 2003 Small Business Server and it all worked happily. I then generated an SSL certificate and applied it to the site in IIS. Initially when you load CRM (using the web client) it loads the left hand menu and navigation panes and shows the top menus and the user which is logged in however to main content area remains blank. After a time out period the error "An error has occurred. For more information, contact your system administrator" appears. The strange thing is you can navigate the menus etc and even edit...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

Speed problems in CRM when merging or deleting accounts
We've been experiencing extremely slow performance in CRM whenever one user is trying to delete accounts or merge accounts. Not only does the deleting and merging take a long time for the user trying to do those tasks; it makes CRM nearly impossible to use for any other users during the time that CRM is trying to delete or merge accounts. Is there something wrong with the delete and merge code that it's locking up the account table during these processes? We have c360 installed. Could that product be complicating the existing CRM delete and merge code? ...

OnGetMinMaxInfo problem
Hi, I added an OnMinMaxInfo() function to my dialog so i can set the minimum tracking width and height for my dialog, I added this prototype to my dialog class: afx_msg void OnGetMinMaxInfo(MINMAXINFO* lpMMI); Then I added this function to set the min and max values: void CMessDlg::OnGetMinMaxInfo(MINMAXINFO* lpMMI) { POINT min; min.x = 200; min.y = 200; lpMMI->ptMinTrackSize = min; CDialog::OnGetMinMaxInfo(lpMMI); } It compiles but i can still resize the dialog to a smaller width/height, Any help would be apreciated :) Thx in advance, Thomas "Thomas" <Da_QuiK@fake...