Sum Question

I am using Excel 2000
I have the following very simple formula in column j Sheet1

=SUM(B5*H5)
I have this copied all the way down to line 40.

It shows 0 in all of the cells all the way down. I would like for there to
be a way that the cell would not show anything in it UNLESS there was
something that it was calculating.

For example. I have entered data in column b and column h
through line 10, but it still shows 0 in column J all the way down to line
40.

I realize that it is showing these 0's b/c I have placed the formula there,
but is there a way that it will still calculate but only if there is data in
column b and h?

I hope this makes sense.

Thanks for any help.

Sincerely,

John Kitchens


0
ironax (34)
11/16/2004 4:48:28 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1112 Views

Similar Articles

[PageSpeed] 43

First, your SUM() function is redundant. It returns the same result as

    =B5*H5

One way to show a null string instead of zero:

    =IF(AND(B5<>"",H5<>""),B5*H5,"")
    

In article <cnd7jp02of1@enews2.newsguy.com>,
 "John Kitchens" <ironax@pineland.net> wrote:

> I am using Excel 2000
> I have the following very simple formula in column j Sheet1
> 
> =SUM(B5*H5)
> I have this copied all the way down to line 40.
> 
> It shows 0 in all of the cells all the way down. I would like for there to
> be a way that the cell would not show anything in it UNLESS there was
> something that it was calculating.
> 
> For example. I have entered data in column b and column h
> through line 10, but it still shows 0 in column J all the way down to line
> 40.
> 
> I realize that it is showing these 0's b/c I have placed the formula there,
> but is there a way that it will still calculate but only if there is data in
> column b and h?
> 
> I hope this makes sense.
> 
> Thanks for any help.
> 
> Sincerely,
> 
> John Kitchens
0
jemcgimpsey (6723)
11/16/2004 4:16:09 PM
JE,

That did it! Thank you so much for your quick response.
Maybe you can help me with somthing else. I posted yesterday under the
Subject line of "Another Macro Problem"

As of yet I have not gotten a response from that post.

Once again thank you.
Sincerely,
John Kitchens



"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-235C2E.09160916112004@msnews.microsoft.com...
> First, your SUM() function is redundant. It returns the same result as
>
>     =B5*H5
>
> One way to show a null string instead of zero:
>
>     =IF(AND(B5<>"",H5<>""),B5*H5,"")
>
>
> In article <cnd7jp02of1@enews2.newsguy.com>,
>  "John Kitchens" <ironax@pineland.net> wrote:
>
> > I am using Excel 2000
> > I have the following very simple formula in column j Sheet1
> >
> > =SUM(B5*H5)
> > I have this copied all the way down to line 40.
> >
> > It shows 0 in all of the cells all the way down. I would like for there
to
> > be a way that the cell would not show anything in it UNLESS there was
> > something that it was calculating.
> >
> > For example. I have entered data in column b and column h
> > through line 10, but it still shows 0 in column J all the way down to
line
> > 40.
> >
> > I realize that it is showing these 0's b/c I have placed the formula
there,
> > but is there a way that it will still calculate but only if there is
data in
> > column b and h?
> >
> > I hope this makes sense.
> >
> > Thanks for any help.
> >
> > Sincerely,
> >
> > John Kitchens


0
ironax (34)
11/16/2004 5:35:50 PM
You have one guess at yesterday's thread.

John Kitchens wrote:
> 
> JE,
> 
> That did it! Thank you so much for your quick response.
> Maybe you can help me with somthing else. I posted yesterday under the
> Subject line of "Another Macro Problem"
> 
> As of yet I have not gotten a response from that post.
> 
> Once again thank you.
> Sincerely,
> John Kitchens
> 
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-235C2E.09160916112004@msnews.microsoft.com...
> > First, your SUM() function is redundant. It returns the same result as
> >
> >     =B5*H5
> >
> > One way to show a null string instead of zero:
> >
> >     =IF(AND(B5<>"",H5<>""),B5*H5,"")
> >
> >
> > In article <cnd7jp02of1@enews2.newsguy.com>,
> >  "John Kitchens" <ironax@pineland.net> wrote:
> >
> > > I am using Excel 2000
> > > I have the following very simple formula in column j Sheet1
> > >
> > > =SUM(B5*H5)
> > > I have this copied all the way down to line 40.
> > >
> > > It shows 0 in all of the cells all the way down. I would like for there
> to
> > > be a way that the cell would not show anything in it UNLESS there was
> > > something that it was calculating.
> > >
> > > For example. I have entered data in column b and column h
> > > through line 10, but it still shows 0 in column J all the way down to
> line
> > > 40.
> > >
> > > I realize that it is showing these 0's b/c I have placed the formula
> there,
> > > but is there a way that it will still calculate but only if there is
> data in
> > > column b and h?
> > >
> > > I hope this makes sense.
> > >
> > > Thanks for any help.
> > >
> > > Sincerely,
> > >
> > > John Kitchens

-- 

Dave Peterson
0
ec357201 (5290)
11/16/2004 10:24:55 PM
Reply:

Similar Artilces:

Excel Question #12
Hello freinds, I have been struggling with this one for some time. Any suggestions will be appreciated. This is a resource utilization report that I want to use to track resources. Here are the inputs - Resource "table", Planner "table" and Allocation "table" Resources ---------- Department X Bob Chris Steve Sue Tim Project Planner ---------------- Project Start Date End Date Proj1 1/1/2004 2/1/2004 Proj2 1/1/2004 1/15/2004 Proj3 3/1/2004 5/1/2004 Proj4 3/1/2004 4/1/2004 Proj5 5/1/2004 12/1/2004 Allocation "table" ------------------ Project PL Ut...

Pasting Data From One Spreadsheet Into Another Question
Hello: Running W98 I would like to insert some data rows from one spreadsheet into another. The data in the older spreadsheet has a row format of date, X, Y, Z The newer spreadsheet has the same format, and I wish to paste several rows into the new ones. The dates would be contiguous, and I have a blank line in the newer spreadsheet where I would like to start pasting Tried the Accounting Wizard, but surprisingly, it can't find the old spreadsheet, even though it is in the Folder that I specified to look in. Is it the Accounting Wizard that I really want to use for this ? What migh...

Newbie question
I am a newbie at Excel and I am going to try to ask this question. I am sorry if it sounds really amateurish, but I have exhausted my "searching for answer on my own" skill. I am trying to create a chart that is comparing 3 different types of data. 2 of which I could combine to put on right axis and one on left. However, when I created a second axis it put my bars right in the middle of the first axis bars and I want it next to it so that I could accurately compare. I can not figure out how to move the second axis bars so that they are next to first axis bars and are not stack...

I truly hope I'll get help with this question
Good day, Using Windows XP, Word 2003 (not for long...) I'm trying to create a table, 5 columns, one line, specific height and width. This is okay. Users will not be allowed to change these settings. However, and here's the tricky part, I want to allow them to change the format applied to the text entered in the 5 columns. For now, it is set at Arial 10, bold. But they may need to change the police size to 9 or 8. I created a style for the table. But if I protect it, unselecting it in the list of styles that can be modified, the protection also applies to the t...

Average of Sum Product
Can you do an average of a sumproduct? I have the following formula in a cell. It's for two data points $12 and $10, so the average should be $11, but it returns $22. =AVERAGE(SUMPRODUCT(($Q$15:$Q$2500)*($V$15:$V$2500="e"))) It seems to be ignoring the average function. Try it like this: =3DSUMIF($V$15:$V$2500,"e",$Q$15:$Q$2500) / COUNTIF($V$15:$V$2500,"e") Hope this helps. Pete On Sep 16, 11:44=A0pm, JimS <jim...@msn.com> wrote: > Can you do an average of a sumproduct? > > I have the following formula in a cell. =A0It's for two dat...

Sum if and statement
i am trying to put together a formula that sums a column based on the information in two other columns. The two columns that need to be evaluated have Dept name and Customer name. there are approximately 80 rows that need to be evaluated in each column, and i want the sum to be only of the values that match the criteria for the Dept & Customer names. assuming your data is in cells A2:C100, Dept Name is col A, Cust Name is col B, and value to sum is col C. =SUMPRODUCT(--($A$2:$A$100=deptname),--($B$2:$B$100=custname),($C$2:$C$100)) Hope this helps. -- John C "Susan" ...

Question regarding Recovery Centre
Hi, Just reading up on Exchange 2003 and wondered if anyone can help with these questions. Q1 : If I wanted to restore a mailbox that was deleted along with an account and it was still in the retention period, would I recreate user account and use the mailbox recovery centre to restore it. Q2 : If the retention period has passed would I have to use the Recovery storage group and recover from backups. (in the MS book it says that If you want to recover a mailbox after retention period you have to use a recovery server ) Regards Joe #1 - yes... #2 - correct...if the account and the ma...

SOP Entry Dexterity Question
Hi Everyone, I'm a Dexterity developer in training and I'm having some trouble with a customization to the SOP Entry form. Essentially we have added the "Salesperson ID" to the header portion of the SOP Entry form and would like the client to be able to change the salesperson id in the header and have any new lines entered use the "new" salesperson id instead of the original value. The problem is that even though you can see that the value is changed in the header, when you save the sales order the changed value does not get saved. As well, additional lines ...

outlook browser question
I just got a new PC with Windows XP Home Edition. After starting it up, I had no problem getting my email set up as I was able to access my old PC email account settings. The look of Outlook was new and I really like it. But here's my problem, I wanted Outlook to open automatically upon starting Windows, so here's what I did. I went into Windows Explorer and copied and pasted the Outlook Express Icon under /programfiles/outlookexpress/msimn.exe into my c:/startmenu/programs/startup directory. This is how I had done this in the past and it worked fine. So, I rebooted to see...

A question about ShellExecute function
Hi all, How to use ShellExecute function to open a html file as following: - Open it in the same window if it is opening in browser (Internet Explorer). - Open it in new window if it has been not opened. Please help me. Thanks. ...

pre-implimentation questions
I'm doing some design work prior to an in-house roll out of CRM 3 and thought it wise to post a couple of general questions. I'm trying to get a better feel for the recommended methods of handling the following scenarios. We will have several different types of contacts (e.g. "Staff" , "Supplier", "Neighbour" etc). A contact can be any combination of these types, i.e. they can be both a supplier and a staff member. If a contact is a staff member, then most users will only get to see a minimal set of data, even if the contact is also a supplier. The ...

Lookup question #4
I have a large spreadsheet which is not a table or database (lot of blank lines). As an example of my problem, in row 500, column F I may have a formula such as =F200. In column C of row 500 I would like to pull the data from column A of row 200. I need the formula in C500 to recognize the reference from F500 and pull the associated info from column A. ...

Action Button Question
I have a button that is unbound. I also have a text box. I want to program the button that when clicked it will place the current date and time at the end of all text (if applicable) in the text box. Let me thank you a head of time Put code like this on the click event of the button. Me.txt1 = Me.txt1 & " " & Now() note: replace txt1 with your own object name. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Timothy Millar" <Tim Millar@discussions.microsoft.com> wrote in message news:F1E6502D-F609-45FC-9F81-03C1B273...

Question #6
I was aksed this question and I'm inclined to say that there is no "legitimate" way of doing this but I want ot make sure that I am correct. There is no way to send an email from thisdomain.com but have it received by thatdomain.com correct. To accomplish this one would have to forge from address etc correct? Thanks Can you re-phrase your question please? The way you phrased your question describes typical mail flow - mail is sent FROM one domain, and received by another domain. I'm sure that's not what you intended. -- Ben Winzenz Exchange MVP "KC"...

A question about MFC dll.
Hi all. I have a MFC extention DLL and I compile it in static link libraries mode. When I use this dll in MFC application which is compiled in shared mode the application failed with this DLL(in static link mode is OK). How can I solve this problem? Please help me. Thanks and best regard. Unless things have changed, MFC extension DLLs must be dynamically linked. Why are you statically linking it? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Binh Nguyen Van" <binhnv@newcenturysoft.com> wrote in message news:#bh6X1X4EHA.3472@TK2MSFTNGP09.phx.gbl... > Hi all. > &...

Question about multiple users errors
Hi, I have inhertied a database split to a back end and a front end, BE is on a network drive and the Front end is loaded on the users C drive. All users have been given full access to the folders. Problem: recently it its been seen that for some reason when one user access the database all others are locked out. This was not the situation before other users were able to use the database at the same time, I'm not sure what caused this problem if anyone has any ideas why this happened or how to fix it, it would be most appreciated. -Steve Have all the users close down the database and...

A question of concept/logic
Hello folks, I would really appreciate your help on the programming logic of the following example: Let's say you have an invitees table with Invitees_ID and Invitees_Names Assuming that you have another table this time with Events_ID and _Name. Now I would like to be able to select invitees, some of them, and invite them to q selected event. Which logic should I use: - Make a third table with gathered Invitees_ID and Events_ID? - Make a table for each events' year so that it can last in the future? Any correction/info will be greatly appreciated :) Have a nice day Use the thir...

Installation Question 10-19-04
Hello, 1. I am running CRM 1.2 on SBS. It has sharepoint on it and two nic's. I created a Web Site called CRMSite an assigned it to one of the nic's. It looks like the Default WEbSite is already using port 80, so I stopped it. I can get to the web site using Http://CRMSite. I only installed the first disk. It created a program group with a couple of tools and no doc's. I need the doc's, but when I try and run the Workflow manager it fails. What should I do next? Michael Michael, When you want to have more websites running on the same port you will need to use "h...

Sum Product
I am trying to get a $ sum based on 2 text conditions, but keep getting a #NUM! error. My formula works only when I limit my search to certain rows. Ideally, here's how I'd like it to work: =SUMPRODUCT((O:O="Medical")*(R:R="Health Care")*(G:G)) The G column contains the $ figures that I'm trying to add. But every so often the column contains a necessary cell of text -- through trial and error, this fact seems to be causing the problem. Any ideas how I can fix this formula? Hi You cannot use full columns in a SUMPRODUCT. Try this: =SUMPRODUCT((O2:O650...

Features Questions
I'm interested in getting Outlook 2007, but I want to know more about the features. Could anyone tell me if it has the following? Design Features Spell checker Mailing labels Customizable notes icons hierarchical to-do lists Customized sort criteria Task timer Reminders Customizable Form Layouts Brainstorming tools Create Custom Fields Email functionality Email client Business card Client-specific email linking Spam filter Spell checker Merge Task Management Tasks in List View Tasks in Tree View Tasks in Calendar View Filter Tasks Sort Tasks Group Tasks ...

Exchange 2003 IMF
I want to know wich punctuantion is that all that mail going to be blocked (1 or 9) Now I have 6 but I need that more mails goes to the users mailboxes and I don�t know if I have to put 7 or 5. Thanks On Fri, 8 Oct 2004 12:06:14 -0300, "Javier Villegas" <jvillegas@elsitio.net> wrote: >I want to know wich punctuantion is that all that mail going to be blocked >(1 or 9) > >Now I have 6 but I need that more mails goes to the users mailboxes and I >don�t know if I have to put 7 or 5. > >Thanks > Setting a higher number will capture more messages,...

Formula Question #22
Probably an easy question, but I can't figure it out. Is it possible to refer to different worksheets in a formula by a cell. Here is an example to help clarify: I want to refer to something on sheet2, which is usually ='sheet2'B2. Is there a way to refer to this if I am working on sheet1 and list a1=sheet2 then type ='a1"B2? I have a ton of formulas that I don't want to change each time. It would be so much easier if I can reference the sheets with a cell. Thanks! Theron Try this: A1 = sheet2 =INDIRECT("'"&A1&"'!B2") -- B...

Import Acces data into Excel using MSQUERY question
Quick question please I'm doing a database query from Excel 2003 in Access. I go: Data/Import External Data/New Database Query/ and then I progess through the wizard, selecting my Access file, selecting the matching criteria and then selecting the cell in the Excel sheet to import to. It grabs the right records from Access, but it imports the data from a record across a row, and I'd like to go down a column, cause that's where the proper target fields are for the fields from Access. Why does it seem I can only import data across a row using MSQUERY? Thanks Harry Don'...

Excel question #9
Is there a way that I can transpose the order of the values in a cell? For example I have the values of 10.200.13.1 in a cell and I want to transpose(not sure if that is the correct term or not) the order of the values in the cell so that they appear as 1.13.200.10. thanks for any help as I have literally 4 pages of these kind of values that I have to flip. -- Brian blanktree at hotmail dot com Hi try the following user defined function from John Walkenbach's book Excel 2000 formulas (great resource by the way): Option Explicit Function REVERSETEXT(text) As String ' R...

Data Comparison Strategy Question
All, I'm hoping one of you Xml or Data gurus can offer an opinion. I'm working on an app to basically compare two semi-structured data files (e.g. Excel / CSV) to one another. But I need to compare them as if they were datatables... (ie a simple diff type tool won't work)... More specifically, the process I envision so far is: 1) Read in two files -- if Excel select appropriate sheet (would also require users to structure their worksheets as tables -- ie no mixed data so common with Excel) 2) Determine worksheet -- if Excel 4) Scan for columns 5) Have user determine which is so...