Despite formula I use (for example SUM) result is always zero.

Despite formula I use (for example the most simple SUM) it always shows me 
zero result even it should show a lot more. Values in other cells (cells that 
I sum) are in number format. Why this happens??
0
Utf
4/23/2010 2:10:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
2368 Views

Similar Articles

[PageSpeed] 26

It doesn't matter whether the cells are FORMATTED to DISPLAY as number; 
what matters is whether the CONTENTS are numbers.  My guess is that they are 
text that might look like a number.  If one of your numbers is in A2, what 
do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
--
David Biddulph


"Anida" <Anida@discussions.microsoft.com> wrote in message 
news:4D40D583-5C85-4470-A427-EC5525D79EB5@microsoft.com...
> Despite formula I use (for example the most simple SUM) it always shows me
> zero result even it should show a lot more. Values in other cells (cells 
> that
> I sum) are in number format. Why this happens?? 


0
David
4/23/2010 2:27:32 PM
This quick fix might suffice for you
Instead of using: =SUM(A2:A5)
use this, array-enter it, ie press CTRL+SHIFT+ENTER to confirm the formula: 
=SUM(A2:A5+0)
The "+0" will coerce all text numbers (if any) within the source range 
(A2:A5) to real numbers w/o impacting their intrinsic values, and return the 
desired results. Happy? hit the YES below
-- 
Max
Singapore
--- 
"Anida" wrote:
> Despite formula I use (for example the most simple SUM) it always shows me 
> zero result even it should show a lot more. Values in other cells (cells that 
> I sum) are in number format. Why this happens??
0
Utf
4/24/2010 2:32:01 AM
When I put =isnumber(a2) it shows me true, and for =istext false. What next?

"David Biddulph" wrote:

> It doesn't matter whether the cells are FORMATTED to DISPLAY as number; 
> what matters is whether the CONTENTS are numbers.  My guess is that they are 
> text that might look like a number.  If one of your numbers is in A2, what 
> do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
> --
> David Biddulph
> 
> 
> "Anida" <Anida@discussions.microsoft.com> wrote in message 
> news:4D40D583-5C85-4470-A427-EC5525D79EB5@microsoft.com...
> > Despite formula I use (for example the most simple SUM) it always shows me
> > zero result even it should show a lot more. Values in other cells (cells 
> > that
> > I sum) are in number format. Why this happens?? 
> 
> 
> .
> 
0
Utf
4/26/2010 8:43:01 AM
Make sure that calculation is set to automatic.

In xl2003 menus:
tools|options|calculation tab

And your range to sum does include A2, right?

Anida wrote:
> 
> When I put =isnumber(a2) it shows me true, and for =istext false. What next?
> 
> "David Biddulph" wrote:
> 
> > It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
> > what matters is whether the CONTENTS are numbers.  My guess is that they are
> > text that might look like a number.  If one of your numbers is in A2, what
> > do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
> > --
> > David Biddulph
> >
> >
> > "Anida" <Anida@discussions.microsoft.com> wrote in message
> > news:4D40D583-5C85-4470-A427-EC5525D79EB5@microsoft.com...
> > > Despite formula I use (for example the most simple SUM) it always shows me
> > > zero result even it should show a lot more. Values in other cells (cells
> > > that
> > > I sum) are in number format. Why this happens??
> >
> >
> > .
> >

-- 

Dave Peterson
0
Dave
4/26/2010 11:26:08 AM
And any chance that the sum is 0?  Maybe you have positive, negative and zero
values in the range?

And one more thing to check.

Type these two formulas in separate cells:
=count(A1:a10)
=counta(a1:a10)

(change A1:A10 to the range you're summing.)

=count() will count the numbers in the range.
=counta() will count numbers, strings, even formulas that evaluate to "" in the
range.

Anida wrote:
> 
> When I put =isnumber(a2) it shows me true, and for =istext false. What next?
> 
> "David Biddulph" wrote:
> 
> > It doesn't matter whether the cells are FORMATTED to DISPLAY as number;
> > what matters is whether the CONTENTS are numbers.  My guess is that they are
> > text that might look like a number.  If one of your numbers is in A2, what
> > do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show?
> > --
> > David Biddulph
> >
> >
> > "Anida" <Anida@discussions.microsoft.com> wrote in message
> > news:4D40D583-5C85-4470-A427-EC5525D79EB5@microsoft.com...
> > > Despite formula I use (for example the most simple SUM) it always shows me
> > > zero result even it should show a lot more. Values in other cells (cells
> > > that
> > > I sum) are in number format. Why this happens??
> >
> >
> > .
> >

-- 

Dave Peterson
0
Dave
4/26/2010 11:28:10 AM
Reply:

Similar Artilces:

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

IE8 uses 95+% of cpu after update to sp3
I just updated to XP sp3 and did updates after that and IE8 is running so slow. I loaded task manager to see what was happening and IE8 was using up to 99%. My system was a slow XP sp2 but did a disk clean and defrag and it was running a lot faster. Apps load and run faster after sp3 but not IE8. Thanks for any suggestions mx5 wrote: > I just updated to XP sp3 and did updates after that and IE8 is > running so slow. I loaded task manager to see what was happening > and IE8 was using up to 99%. > > My system was a slow XP sp2 but did a disk clean and defrag and...

assign numeric value to letters and sum with other numbers
I apologize if I am duplicating an earlier question, but I can't find the answer. How do I sum a row or column that has numbers and letters by giving the letters a numerical equivalent? -- WJG On Mon, 11 Jan 2010 12:19:01 -0800, Galadad <Galadad@discussions.microsoft.com> wrote: >I apologize if I am duplicating an earlier question, but I can't find the >answer. How do I sum a row or column that has numbers and letters by giving >the letters a numerical equivalent? Could you give an example of input and expected output. Lars-�ke Just guessing...

Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....

if formula #2
i have this formula in a cell D3 =IF(A1="F",B1&B2) how can i make it multiple for example i want to add this IF(a2="G",B1&B3) in D3 also. thanks Either - =IF(AND(A1="F",A2="G"),B1&B3,) =IF(OR(A1="F",A2="G"),B1&B3,) Regards. Bill Ridgeway Computer Solutions "jaypee" <jaypee2cool@yahoo.com> wrote in message news:O3NhGKIhGHA.1272@TK2MSFTNGP03.phx.gbl... >i have this formula in a cell D3 =IF(A1="F",B1&B2) > how can i make it multiple for example i want to add this IF(a2="G&q...

Inserting formulas with PL/SQL
I'm not familiar with PL/SQL, but my friend is using it to place data into an Excel spreadsheet. She can place strings into the spreadsheet, but cannot figure out how to put in formulas. Specifically, she is trying to place web links into a column of cells. She is able to put in 'http://www...' as a string, but then cannot link from the cell. I suggested using Excel's hyperlink function, but again it ends up as a string, not a function. .... .... owa_util.mime_header(ccontent_type =>'application/vnd.ms-excel'); htp.p('ID;ORACLE'); htp.p('...

How to make a column of formulas all ROUND
I created a spreadsheet in which I have a column of formulas. Most of these fomulas are simply pulling a single number off another sheet. I want to make all the formulas ROUND versions of the existing formula without having to go into each cell and making the change. They are not in order to which I can just make the first fomula a ROUND fomula and copy down. So, is there a way to select a range of cells and make the existing fomulas all ROUND versions? Thanks. Would this help? Sub RoundAdd() Dim mystr As String Dim cel As Range For Each cel In Selection If...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

How to use CSplitterWnd for Dynamic Nested Splitter window?
Using VC++ (.NET) I am trying to implement a Dynamic Nested Splitter window using CSplitterWnd. I haven't found any documentation that explains how to get this to work properly and the only examples I have found also do not work properly. There are plenty of examples of Static Nested Splitter windows, but I need an example of a Dynamic one. The static splitter windows won't work for my requirements. Are there any examples or documentation that explains how to properly implement a Dynamic Nested Splitter Window using CSplitterWnd? Thanks, Dave Hi Dave, > Using VC++ (.NE...

EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...

Compare 2 Worksheets Create a 3rd depending on results
I have a unique problem that none of the other posts or shareware seems to solve. I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to the "O"). The data is just numbers but the second column either has the words: "new" or "cancel" which is important as you will see. I need to compare the two worksheets and create a third worksheet (update.xls) depending on the three possible results: 1) If a row is removed in the new.xls file = copy the row from the orig.xls file and make the 2nd column "Cancel" 2) If a row is added to the new.xls...

short cut for copy past w formulas??
I hope I can explain this right. If Sheet 1 cell 1A were to equal Sheet 2 Cell 1A I need a quick way to copy that formula so on sheet 1 cell 9A will = sheet 2 cell 2A , sheet 1 row 17 A = Sheet 2 Cell 3A and so on .. If i do a straight copy past it equals the row on sheet 2 which is no good to me.. I only have about 300 cells to do so any short cut would help. Thanks in advance. In sheet1 cell A1 enter the formula =OFFSET(Sheet2!$A$1,TRUNC((ROW(A1)-1)/8),0) Then copy this down to every 8n+1'th row -- Return email address is not as DEEP as it appears "DR, Bob" <bob@mai...

Coding Convention for using Binary FlagWords?
Hi all --- I'm thinking of putting the .Tag property to use by using a binary ("bitwise") encoded flag word. Over time I've come across different possible uses for .Tag but have never standardized how I use it. Has someone developed a convention for "parsing" binary flag words? A simple IF works if I only need to test for one flag, but if I need to check for the presence of multiple bits the only construct that comes to mind is a series of IF statements .... something along the lines of: Enum FlagWord FlagA = 1 FlagB = 2 FlagC = 4 .......

Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...

Weekly spending sums
I have several separate worksheets in one file containing financial data with purchases made on certain days, eg. one of which is called "items": article, quantity, price, date[DMY] screws, 1000, 3, 14/10/03 washers, 2000, 4, 25/11/03 bolts, 250, 5, 03/12/03 nuts, 500, 10, 04/12/03 On a separate sheet I would like to collate that data by weeks in a "sum" sheet, eg. start, end, money_spent 13/10/03, 19/10/03, 3 <-[*] 20/10/03, 26/10/03, 0 27/10/03, 02/11/03, 0 03/11/03, 09/11/03, 0 10/11/03, 16/11/03, 0 17/11/03, 23/11/03, 0 24/11/03, 30/11/03, 4 01/12/03, 07/12/03...

Using Outlook client for CRM 3.0 in a remote office
I need to set up access to the corporate CRM from several remote offices. All of them have VPN connections (Windows or Checkpoint). What are the steps required to configure remote clients to be able to use CRM features in Outlook. Remote users can connect to CRM through Internet Expolorer. Thanks. Assuming they are connected, the isntallation over a VPN connection should not be any different than a typicaly installation. If the connection is slow though, the first synch & Go Offline process will be noticiably slower though. -- Matt Parks MVP - Microsoft CRM "mkatsev"...

using std::deque in multiple threads
I have a thread that listens on a message queue and populates a std::deque with events from that message queue, but only holds the latest 100. In my main thread, I want to populate a listbox with the information from that deque. So, it ends up being that one thread can add/remove items from the deque while another thread is trying to iterate through them. How can I make this thread-safe? Thanks, PaulH The code looks a bit like this: std::deque<MESSAGE_TYPE> m_dequeMessages; CMyClass::MessageThread() { //... while (ReadMsgQueue(hMsgQueue, &msg,...)) { m_dequeMessages.pu...

Wordwrapping a long sentence using F2, how to end the process?
Using Excel 2002...I pasted in a long sentence, and it goes off the right side of the screen. I know that I can make it word wrap by hitting F2. I've done that and it works nicely. But I was told that I could end this process by hitting Alt-Enter, and that doesn't work... all that happens after Alt-Enter is a blank new row opens up below the word-wrapped rows. If I hit Alt-Enter again, another blank row opens up below. How can I end this F2 word-wrap and go on with other business. John alt-enter is used to force a new line in a cell. If you want to wrap the text a...

Use a wildcard within edit/replace
Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)),"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D:$E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImport!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefo...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Mobile using up CPU
Hi there Since we installed CRMMobile our (new) server has been SO SLOW! SqlServer is taking anywhere from 69 - 99% cpu time (mostly around 99) I have run sql profiler and discovered that many times a second the following transactions are running: exec sp_executesql N'SELECT recipientAddr,recipientSrvcPort,sourceAddr,sourceSrvcPort, MIN(arrivalDate) as minArrivalDate, count(*) as messageCount FROM vwInBoundQueue WHERE recipientAddr=@recipientAddr AND recipientSrvcPort=@recipientSrvcPort GROUP BY sourceAddr,sourceSrvcPort,recipientAddr,recipientSrvcPort ORDER BY minArrivalDat...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

File in use .... is locked for editing
Please help. In Microsoft Excel 2000, we seem to be getting the above error message frequently. No other users are in the file, and there aren't any temporary files relating to the file in question. Any ideas on what is causing this? We are running a mixture of Windows 2000 & XP machines on to a Small Business Server 2000. Hope to hear from someone soon Regards Simon I think you killed the usual answer of cleaning the temp folder. But have you rebooted? Maybe there's a hidden instance of excel running that "owns" the file. And if the file/workbook is on a net...

Specify data for use in FRx pivot tables
How do I specify what data fields appear in an Excel pivot table (or ..cub file) from FRx? I want to export the natural account number into the pivot table to sort on. TIA ...

using $ in a formula
when in a cell and I type: =$C4, what does that mean vs =C4? Also what does =$C$4 mean vs =C$4? what is the significance of the dollar sign? Thanks Hi Robin it changes the addressing from relative to absolute e.g. if i have in cell C1 the formula =A1*B1 and i fill it down to C2 the formula will change to =A2*B2 or if i fill it across to D1 the formula will change to =B1*C1 - this is relative addressing and is the default in excel however sometimes you want to "fix" the cell reference in a formula and this is where the $ come in =A1*$B$1 filled down from C1 to C2 will change ...