SumProduct using greater than

I have the following formula

=SUMPRODUCT(('All Resource Plan'!$B$3:$B$42=Principal)*
('All Resource Plan'!D$3:D$42>0))

Where B3 to B42 on the all resource plan tab have a 
variety of roles including principal.

On the same tab d3 to d42 is a range of numbers from -1 to 
+1.  

I need to sum all values associated with a role that are 
greater than 0.  I was using this formula with an =1 and 
it worked fine, however when I changed it to >0 it no 
longer works.  

Any help would be appreciated.
0
anonymous (74722)
2/16/2004 3:58:16 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
325 Views

Similar Articles

[PageSpeed] 43

First of all, you are counting not summing, secondly the formula works for
me.
If you want to sum use

=SUMPRODUCT(('All Resource Plan'!$B$3:$B$42="Principal")*('All Resource
Plan'!D$3:D$42>0),'All Resource Plan'!D$3:D$42)

it obviously won't matter if you want to sum >0 and there are only -1 0 and
1 but if you want to sum <0
it will matter since you'll get a positive result using your formula. That
might not answer what is failing
but it is certainly not the formula, most likely it's the data, maybe your
values are text? Also what
does not work? An error, computer exploded, expected a different result?

-- 

Regards,

Peo Sjoblom


"Jeff Goldstein" <anonymous@discussions.microsoft.com> wrote in message
news:109f101c3f4a5$b0bd6810$a301280a@phx.gbl...
> I have the following formula
>
> =SUMPRODUCT(('All Resource Plan'!$B$3:$B$42=Principal)*
> ('All Resource Plan'!D$3:D$42>0))
>
> Where B3 to B42 on the all resource plan tab have a
> variety of roles including principal.
>
> On the same tab d3 to d42 is a range of numbers from -1 to
> +1.
>
> I need to sum all values associated with a role that are
> greater than 0.  I was using this formula with an =1 and
> it worked fine, however when I changed it to >0 it no
> longer works.
>
> Any help would be appreciated.


0
terre08 (1112)
2/16/2004 4:14:25 PM
Of course...I didn't realize that I was counting as 
opposed to summing.  When I made the mod suggested it now 
works (it is summing the values that I need).

thank you,

Jeff
0
anonymous (74722)
2/16/2004 4:45:18 PM
Reply:

Similar Artilces:

What template do I use for Avery label 8395 in publisher
Label 5895 in page setup, it is the same size as 8395 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "suzannett" <suzannett@discussions.microsoft.com> wrote in message news:08305733-E0E2-4954-B425-E4B2CCE44626@microsoft.com... > ...

using Xmlserializer for Char datatype gives problems
I have a class that I serialize/deserialize using xmlserializer. There are a few properties with char datatype. I have a few xml files from which I want to create the objects. While deserializing from xml if the properties having char datatypes have numeric values(0-9) then everything worls fine. If I put any letter e.g. A it starts throwing exception - "Input string is not in correct format". If I convert these properties to string, all data works. But I have to keep these properties as chars for some reasons. Is there a problem with the char datatype and xmlserialiser? Is there any...

Problems accessing other's mailbox using OWA
Hi, I'm running Outlook Web Access (forms based) in Exchange 2003 native mode. Logging into our own mailboxes is fine, but when trying to access another persons inbox, calendar, etc, I get a "Page Cannot be found" error. When in my own mailbox the URL is: https://mailserver/exchange/ In order get into another user's calendar (for example) I'm entering: https://mailserver/exchange/user1/calendar I am listed as a delegate of user1's calendar. Does anyone have any thoughts on what I'm doing wrong? Thanks, Curtis. -- Please reply to news group only. Thank...

GAL Extraction VB6 Sample Code using Redemption
Private Sub Form_Load() 'create a form, drag in a command button End Sub ' ------------------------------------------- ' The process is simple but sometimes getting there is painful. ' Redemption.dll is the only MAPI based data object required ' Download site is: http://www.dimastr.com/redemption/ ' After Installing: Project | References | [Browse] to installed folder ' Upon selection the reference is called "SafeOutlook Library" ' ------------------------------------------- Private Sub Command1_Click() Dim fld(15) As String ' http://su...

Excel 2007 using combo Boxes
please can someone help me, In Excel 2003 i used Combo boxes which were in the forms tool bar and in format control had a cell link used for counting. I cannot find in Excel 2007 the forms tool bar or Combo boxes with the cell link, Can any one point me in the right direction of how to use combo boxes in Excel 2007 thanks > please can someone help me, In Excel 2003 i used Combo boxes which were in > the forms tool bar and in format control had a cell link used for > counting. I > cannot find in Excel 2007 the forms tool bar or Combo boxes with the cell > link, Can any one...

Using Acces data in to send personalized letter
we have created an access database with names address etc; the input screen works ok for these fields BUT we want to be able to input the required data then have extra buttons that will allow the printing of a personalized letter for the data just input/onscreen and save it at the same time, and a separate button incase we just want ot save but not print. Sorry if you guys think it's simple but we are very new to this. Regards Adrian Hi Adrian, There are a number of ways you could print a personalized letter. You could create a Report each time and copy the record data to the relev...

newbie: How to use WriteXmlSchema?
I must be missing something simple. I want to serialize a DataSet to local disk when my .NET 2.0 WinForms app closes, and deserialize the DataSet when the app reopens. I use this code to serialize my DataSet: XmlSerializer ser = new XmlSerializer(typeof(DataSet)); TextWriter writer = new StreamWriter(xmlFile); ser.Serialize(writer, projectData); projectData.WriteXmlSchema(xmlSchema); //must this be written every time? writer.Close(); Here are the results: <?xml version="1.0" encoding="utf-8"?> <DataSet> <xs:schema id="ProjectData" xmlns=&qu...

How do I set up dowloading PDF file on web page using Publisher
I am setting up a simple web site using publisher and what to give browsers access to PDF files of media articles etc. How do I set up a link on a web page and where do I put the main PDF file? On your webpage put a text line for the file, i.e., Get this file. Highlite the word FILE or words GET THIS FILE. Right click select Hyperlink and type in the path to where you are storing the pdf file, i.e., downloads/file.pdf Create the folder DOWNLOADS on your website, upload file.pdf into the DOWNLOADS folder. (uppercase is to clarify suggestion and not necessary for actual procedure - al...

How to generate xsd file using <xsd: and not <xs:
When I generate an xsd *schema* I get something like : <?xml version="1.0" encoding="utf-16"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="AccountsReceivable"> .... but I want to have the following, and don't know how to achieve it <?xml version="1.0" encoding="utf-16"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:element name="AccountsReceivable"> .... "andy gilman" <andygilman@discussions.microsoft.com...

using dates
I am wondering if anyone can help me parse a date field and update another field. Here's the situation: I have a date coming thru - for example: 2/1/2010 I need to populate 3 fields based on this date 1) B_Qtr - 2011-1 2) Year - 2011 3) Qtr - Q1 Our FY year is a little different in that it starts in February and has the next year on it -- Thank you in advance!!!! Try these remembering data from Format function is text -- 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[Act...

Which chart type to use
This should be pretty simple, I need to compare the body weight and brain weights of 3 different animals. THere is a signifigant difference between the brain and body weight of each, for instance, Humans are avg 160 lbs and brain is 3.5 lbs. I tried a column chart but you dont see the brain data, it is too small compared to the body weight. What type of chart would best display this data? Thanks. XY chart, body weight along X, brain weight along Y, data labels to identify the species. Use one of these utilities to provide the custom data labels: Rob Bovey's Chart Lab...

Can I use Office among 4 users on one machine?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I'm using the Mac's "fast user switching" option on my new mac (4 users total). I'd like be able to have any of us edit a document at any time, without having to switch users and close an Office product that another user has open. Can I do this? Whether I can or not, can anyone point me to where its documented? (the Home edition license agreement seems to say I can install and use up to 3 copies on one machine - is this 3 copies in addition to the original installation or including the original? Als...

Can't send email using IMAP
I am having a problem setting up clients using IMAP to reply or send outbound mail. They can access their inbox and sync mail without a problem. They connect using a VPN. Exchanger Server 5.5 ISA Server 2000 Spam Filter on a different machine. I have searched for two weeks and still can't find a solution. Please help as this is extremely important. Carl Hi, IMAP uses SMTP to send mail.. Have you published SMTP through your ISA server so it's available? Are these internal or external users? See if they can telnet to the (internal or external IP depending on user location...

How do I reuse label sheets when I don't use the whole page?
I need a way to print only 2-3 labels at a time, but not waste the whole sheet of labels. Any ideas? I have tried every option in Access that I can find. Ralph, Yes it can easily be done. If you google you will find lots of examples showing you how this can be done. Below is one link http://books.google.ca/books?id=IDxCIGNjNTYC&pg=PA159&lpg=PA159&dq=ms+access+vba+skip+label&source=bl&ots=phQIJ4ahfz&sig=l66-wBJVYdq4NZP4i806iB4iTdg&hl=en&ei=4HXYSoqXBJSk8Aaas4m3BQ&sa=X&oi=book_result&ct=result&resnum=8&ved=0CCIQ6AEwBw#v=onepage&...

Hanging 5 minutes on send when using rpc over http
Hi folks, I'm using a Outlook 2003 client connecting to a 2003 server with rpc over http. The profile is setup for cached mode if that is of interest. So my problems is that in one office the internet access is only via a local proxy. This proxy is configured in IE and I can connect fine and I get my messages downloaded. When I try to send a message Outlook is hanging for a couple of minutes and more or less hanging the whole system. After a while th message is sent fine. In another office I also do have a proxy connection and there everything is working fine. What is going wrong? Can ...

Building Gantt Chart using FlexGrid- how to start?
Hello, I need to build a Gantt Chart using data in Access. I am creating a production schedule with multiple tasks and resources; it is quite complex. I found the FlexGrid demo and it seems to be useful, but as a beginner in Access I'm not sure how to start. Is there a walkthrough or step-by-step that will show me how to make the chart? Thanks in advance, Emily I'm not sure that the flexgrid is going to work for this. It's going to require your entire column be the same width, so that's going to make it tricky to show variable widths in your graph. I could see doing...

which shape should i use ?
Hello. I been using visio pro.. to drow Networking diagrams so i only got information about limited sets of avalible shapes. Now , i need to create database for my new project. I know the shapes that i have to use for database. but i need to know, is there any other shapes that i can use to describe attributes for products? ie: before creating db. i need to define prodcuts and thier attributes, Users and thier attributes and soo on . which shapes whould you recommend for those process ? Thanks in advance ...

Protect worksheet but still be able to use Data Filter and Data Sort
I worksheet that I want to protect but I would like the users to still be able to use Data Filter and to be able to sort the Data. Is this possible in Excel 2003 without using macros? When I select Tools/Protection/Protect Worksheet I have notice there are check boxes which allow exceptions to the usual total protection. I have ticked the following boxes: Allow all users of this worksheet too: Select locked cells Select unlocked cells Sort Use AutoFilter However I am still unable to apply autofilter or sort the data. Have I missed a step? Or am I asking for the impossible? Any hep with this ...

Users only using OWA + change password at next logon
We have some users that really never logon to a workstation, but they do need their email, which they will check through OWA. (Exchange 2003) How do we handle those users as far as password expiration and "User must change password at next logon" (after creating a new user) goes? It seems like both of those things only popup if you logon to a workstation joined to the domain, but not through OWA, at least not by default? Any suggestions really appreciated! TIA!! / Per See if this helps. http://support.microsoft.com/kb/833734 SUMMARY The Password Change pages that are...

using setup project
Hello! Is it possible to use setup project that exist in VS2005 to deploy application written in MFC. I know this can be done if you have written classes end exe files in for example C#. //Tony "TonyJ" <johansson.andersson@telia.com> wrote in message news:eF9xIY0KIHA.5360@TK2MSFTNGP03.phx.gbl... > Hello! > > Is it possible to use setup project that exist in VS2005 to deploy > application written in MFC. > > I know this can be done if you have written classes end exe files in for > example C#. > > //Tony > > Yes, the setup/installation pr...

AdventureWorks: can I query table w/o using table_schema name
I tried to query the Contacts (Person.Contacts) table in the AdventureWorks DB, but it wouldn't let me unless I included Person. in front of the table name: select * from Person.Contact If I query the same table from a VS(2008) project using LinQ To Sql, this appears to forgo the table_schema name. Is there a way to forgo the table_schema name in QA? Thanks, Rich I don't know what "magic" LINQ is doing, but at the TSQL level: If you don't specify a schema, then SQL Server will look at the default schema for your user. So, make sure your user has t...

Running Total using a Query
I am trying to create a running total of charges to a person's account in a query. I have been able to get the running total to work but it only does the running total for one month and then starts over, while I need the total to keep going month to month and year to year. I have this in the field line with "Total" set to "expression": RunTot: DSum("[Curr Dedn]","GarnAmountsTable2","DatePart('d',[Check Dt])<=" & [ADay] & " And DatePart('m', [Check Dt])<=" & [AMonth] & " And DateP...

Anyone using Crystal Enterprise with CRM 3.0?
Interested if anyone is using (or considered using) Crystal Enterprise/Viewers (for whatever reason) with CRM 3.0 and would like to hear any lessons learnt. (One of our clients has an existing Crystal Enterprise system, and on the face of it there could be savings from having to upgrade thirty custom crystal reports created for CRM 1.2) Thanks Peter ...

Using Custom Lists
I understand how to create a custom list. I just don't know how you use the list. Please Help Vampire Custom lists are used when you are auto-filling down a column or across a row. Examples of Custom Lists are months, days, alphabet and of course, your own created list. Basic use..........Enter first object in the list then drag to get the rest of the list auto-filled. i.e. Enter January in A1. Grab fill handle at bottom right corner of A1 and drag down the column. Gord Dibben Excel MVP XL2002 On Sat, 6 Sep 2003 13:52:13 -0400, Vampire542 <Vampire542.tcitn@excelforum.com>...

Using Format and DateAdd
Hello, can't understand what I'm doing wrong here.. I'm getting a 'The expression you entered contains invalid syntax' message. =DCount("[Client_ID]","tblClients","Format([d_received],"mmyyyy") = #" & Format(DateAdd("mm",-11,[Forms]![frmReports].[txtYourEndDate]),"mmyyyy") & "# ") I'm trying to get the total for each of twelve months into text boxes. The above code, I hoped, would give me the total number of clients received in the first month of a twelve month period. The text box givi...