#### 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.

3 Replies
775 Views

Similar Articles

[PageSpeed] 35

```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

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

Random Number Question
Does anyone have a Random Number Formula for creating the following Random Numbers in 52 different cells with no number repeated Yes I am working on a Poker Rally and need to generate score cards that are different and Random Steve Boyle By the way thanks for the help on my last question. If I ever remember to take the print out to work and try the answers I will let you know how well it worked. Steve, This should be exactly what you need. http://tinyurl.com/2jgd8d John Wilson "Steve Boyle" <waylandboyle@comcast.net> wrote in message news:ACDCCD1E-D8C6-4A1A-BCE5-...

2 questions #2
hello, Here is my first question,money plus keeps downloading my statement but will only use 1 payee.How do i fix this. Question 2 Money has decided that it will now import my statement for my checking account and put it into my savings account.How do i fix this I am using money plus deluxe version 17.0.120.1415 purchased in the us and i have been using this program for almost a year.Any help would be appreciated In microsoft.public.money, sondra99999 wrote: >hello, > Here is my first question,money plus keeps downloading my statement but >will only use 1 payee.How ...

e-mail distribution group question
Here's the situation: User A's incoming e-mail are delivered to user A and user B. User B's incoming e-mails are also delivered to user C. BUT user C does not want user A's e-mails... At the moment User C gets both user A and B's e-mails Is there a way I can set this up? Many thanks M Hi, Do they need to receieve copies of others emails in their own mailbox? You could give permissions to read the other users mailboxes. Then you of your clients would just need to add them as an additional mailbox in Outlook. The other option is to create a server side rule in outl...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Another sorting question
Finally figured out how to sort my information but this is my next question. Everything is sorted alphabetically by the first column (Name)...when I enter a new name now, will it automatically sort? Or - do I have to do this exercise of sorting on a constant basis. Thanks! my experience say you have to sort manually every time unless you I import it into Access and generate queries or reports. you included details about ...alphabetically by the first column... is that what you want? you can queue it to sort by which ever column(s) you want. "Terri" <terri@mylanusa.com&...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...

xpath question #5
I use in my csharp code this xml: <doc> <item Id="1"> <item>1</item> </item> <item Id="1"> <item>2</item> </item> <item Id="2"> <item>A</item> </item> <item Id="3"> <item>B</item> </item> </doc> And I need to exclude all items that Id="1" and then in the result set, get the a range of nodes, for exmpale the first to third nodes. I use this /doc/item[@Id != "1" and (position() >0 and position() <= 2) ...

Excel 2000 HTML/XML format Question
Hi everyone, I have a question about the HTML/XML format of Excel 2000 when you sav a workbook as a web page. I have a requirement where I generate the HTML/XML structur programatically and save it in seperate files (one .htm file for eac worksheet). Everything works fine. However, I was trying to cra everything in one single file and experimenting with various options. For example, I have the following xml structure that creates the tw sheets. Code ------------------- <xml> <x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:Name>Sheet1&...

Question on Windows Live Mail Calendar
Probably dumb question but here goes... Is there not a way to set an alarm of some sort, for an event scheduled on the Windows Live Mail calendar? Like I schedule say a doc appt for a specific date and time, would like to have some sort of a reminder that it is coming up? I know I could do that in the older Windows Mail calendar. -- Don Yes. On the desktop WLM app. there's a little "bell" icon when you create a "New event", which offers alarm options. "Don" <burnettedclothes@hotmail.com> wrote in message news:GdCdnb...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

Number formatting question
I have hundreds of rows of numbers (example 234553) it need to add a decimal to every row to make it 2345.53. Is this possible? Yes One method Put the value 100 in a blank cell. With that cell selected, Edit/Copy Select the numbers you wish to change (highlight the column) Use Edit/Paste Special and select the Divide option. HTH PC "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message news:74C0C90C-6C93-4055-8983-430D97B09692@microsoft.com... > I have hundreds of rows of numbers (example 234553) it need to add a decimal > to every row to make it 2345.5...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Weird question on filling in typing in excel
I have a question about fill in cells in excel kind of like a list or a combo box. I have someome that wants to be able to start typing in excel and have the rest of the characters popup like the url address does in IE. I don't think that this is possible in excel, but I could be wrong because I'm not that familiar with it. I would think that it would have to be backed up by a database for this to work. Could someone help me on this one. I would like to initially do it with out a DB behind it. I have ID#'s and Client names that I would like to popup into the cells after typi...

I have the following questions: How do I organize the address book to look like it does in Outlook - i.e. - detailed address cards? Can I change the color palette in Entourage? "Craig Schiffer" wrote: > How do I organize the address book to look like it does in Outlook - i.e. - > detailed address cards? I've never used Outlook so I couldn't even guess at how it looks. You need to post screenshots and give details of what you want the Address Book contact to show. You can post a screenshot link using a service like ImageShack. <http://imageshack.us/> How To...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

CDC::GetTextExtent question
Hi, I have a function that is to resize a status bar text pane when setting text dynamically. GetTextExtent() seems to think the text is almost twice as wide as it actually is so it leaves too much white space. How come? This is my func.: BOOL CMainFrame::SetStatusText(UINT nID, LPCTSTR lpszText) { int index = m_wndStatusBar.CommandToIndex(nID); if (index < 0) return FALSE; UINT nIDRet, nStyle; int cxWidth; m_wndStatusBar.GetPaneInfo(index, nIDRet, nStyle, cxWidth); ASSERT( nID == nIDRet ); CSize sz(cxWidth, 0); CDC* pDC = m_wndStatusBar.GetDC(); if (pDC) { sz = pDC->...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Fiscal Year Set Up Question
We would like to change our fiscal year from 12/31 to 6/30. Specifically, we are now in December, the end of our 2006 year. We want to be able to end 2006 on 12/31, but start 2007 on 1/1, and end it on 6/30. Can we just create the 2007 fiscal periods with 6 periods ending on 6/30? And what are the ramifications if, come June 2007, we needed to continue on with a 12/31 year-end... Could we just "extend" 2007 for 6 more periods? Or would we be locked in to those 6 periods? Or... looking at it the other way, what if we set up 2007 with 12 periods, then come, say March, decided that...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

Category Group Question
I am currently using Money 2003. I have used various versions, but only as a glorified check register. Now I am trying to learn how to use it better. I went under Categories and see that most of Categories are not assigned to Category Groups. What are the Category Groups used for? Also, why is there a Category Group for Rent Bill but none for Mortgage Payment? Should I put my Mortgage Payment under Household Expenses? Thanks. Nobody's quite sure what the cryptic category groups are used for. See http://www.bollar.org/msmoney for a FAQ answer on the subject. "TimothyR" ...

Message routing
Hi Have the following problem with message routing - Exchange 2003 is configured to be responsible for email sent to domain @xxx.co.uk (set as primary) and @xxx.local in default recipient policy. This is required so that the reply email address for users can be set to user@xxx.co.uk The problem we have - when a user on the LAN sends an email to an external consultant @xxx.co.uk, Exchange tries to route it internally and generates an NDR as that user does not have a local account on the server (consultants work remotely and collect their email from the company that hosts the domain) Do I have...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

Autoformat question
We have a need to turn off AutoFormat replacement of "straight quotes with smart quotes", "fractions with fraction characters", "ordinals with superscript", and "hyphens with dashes" on a 1,000 of our 13,000 installations of Office 2007. I can use a specially filtered group policy with the Office 2007 group policy templates to disable those options in AutoFormat As You Type/Replace As You Type, but there is not a similar group of settings for AutoFormat/Replace. Does anyone have any idea how I can accomplish this aside from emailing man...