sum cells

I am trying to total employee schedule so some cell have D/O day off, R/O 
requested off, so when I try to total all associates by the day I get #VALUE! 
in cell and total cell. Is there a way to total only numbers and ignore 
#VALUE!
0
SteveD (30)
10/24/2006 4:34:04 AM
excel 39879 articles. 2 followers. Follow

2 Replies
496 Views

Similar Articles

[PageSpeed] 1

Try this:

=SUMIF(A1:A10,"<>#VALUE!")

Why not fix the formula that causes the error in the first place?

What is the formula that does this:

>when I try to total all associates by the day I get #VALUE!

Biff

"SteveD" <SteveD@discussions.microsoft.com> wrote in message 
news:7D8BF85A-F76E-4F35-95AC-50B77B128304@microsoft.com...
>I am trying to total employee schedule so some cell have D/O day off, R/O
> requested off, so when I try to total all associates by the day I get 
> #VALUE!
> in cell and total cell. Is there a way to total only numbers and ignore
> #VALUE! 


0
biffinpitt (3172)
10/24/2006 6:05:53 AM
In line with Biff's reply, you should ALWAYS try and fix your data at source, 
rather than implement workarounds to cater for error values.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"SteveD" wrote:

> I am trying to total employee schedule so some cell have D/O day off, R/O 
> requested off, so when I try to total all associates by the day I get #VALUE! 
> in cell and total cell. Is there a way to total only numbers and ignore 
> #VALUE!
0
ken.wright (2489)
10/24/2006 7:53:02 AM
Reply:

Similar Artilces:

Build value from cell content & sheet name !
Hi All, Interesting query (I hope).... I'd like to create a value in a cell that represents a concatenation of a value in another cell and the sheet name (tab name). Example, sheet name is 'A' and value in cell 'M15' is '1162'. I'd like cell 'M18' to indicate a value of '1162A'. Any ideas ?? Thanks, Neil =M15&sheet_name to get sheet_name see http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH RP (remove nothere from the email address if mailing direct) "Neil" <me@home.com> wrote in message news:dekai3$1p...

How to use cell data in formula after splittling cells
Hey, I pasted a text document into excel which i wanted in three columns date, price and volume. I successfully split these up using "conver text to table". What I want to do now is use the price data in a formula, but it keep coming up with "VALUE" error. Can anyone tell me what I have done wrong and how I can fix it? Thanks heaps!! -- Message posted from http://www.ExcelForum.com Hi sounds like your values are actually stored as text. Try the following: - select an empty cell and copy this cell - select your price and volumn data - goto 'Edit - Paste Special...

Formula to say Yes or No based on variance in time cells
Hi all, I have 2 time cells, F5 and I5, I want a forumla that will return the result Yes or No if there is a specified difference, 30mins for example. We have a spreadsheet with the same information coming from 2 sources and need to compare the information to say wether it is similar or not. Thanks in advance for any help Absolute difference? =IF(ABS(F5-I5)>=TIME(0,30,0),"Yes","No") -- HTH Bob Phillips "KingCreole" <shamble@gmail.com> wrote in message news:1117014247.379550.142950@z14g2000cwz.googlegroups.com... > Hi all, > > I have 2 ti...

Code places sum at bottom, require it on top of range
I have a =sum that needs to be inserted one cell above the range (number of rows will vary but always in column "E"). The code below inserts the code one cell below the range but I'm not having any luck figuring out how to change it so that it inserts it one cell above. Any ideas? 1 2 3 4 -10- but I require -10- 1 2 3 4 Worksheets("SecondSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row, 5).Select x = Range(ActiveCell, ActiveCell.End(xlDown)).Count ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 = "=sum(R[-" & x & "]C:R[-1]C)" Dim iLa...

Country Prefix in same cell
Hey All! I'm trying to pull country prefixes for matching countries from the second sheet that has recurring countries with respective prefix to the first sheet that is not recurring, prefix for same country in one cell separated by comma. Please see attached link for a visual description. Will appreciate your input. Link: http://www.excelforum.com/attachment.php?attachmentid=4160&d=1135879308 Hi Ely, try using a Pivot Tablej or a macro solution http://www.mvps.org/dmcritchie/excel/snakecol.htm#joining.htm of course this is just using the second sheet as input in creating...

filtered cells
hello, I've got in sheet 1 and 2 a autofilter and I want to copy the filter results to sheet 3. Is that possible and does anybody know how to do it? greets, mark Hi You need to copy visible cells only once filtered required data select edit goto special select visible cells ok then copy this to sheet 3 Tina "minostrada" wrote: > hello, > > I've got in sheet 1 and 2 a autofilter and I want to copy the filter results > to sheet 3. Is that possible and does anybody know how to do it? > > greets, > > mark Hi tina, I don't exactly know wh...

keeping cell references during sort
I have data that I am summing. For example, I have B1 =SUM(A1:A3). Then B2 = SUM(A4:A6). I need a way that if I sort the data in column the cell references in Column B update. So if my B1 data is no contained in A5, A12, A15, my B1 formula will update to B1 =SUM(A5 A12, A15). Is there a way to do this? Thanks, Erik Spark ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com The formula in B1 won't change when column A is sorted -- it will continue to sum A1:A3. You ...

Insert copied cells above hidden Named Range
I'm not sure what happened here - this worked in the past, but suddenly there is a problem with "Selection.Insert Shift:=xlDown". The "record end" named range is still there, so it's not that. The marco is supposed to go to "RecordEnd" which is hidden, unhide it, insert the copied cells while pushing "RecordEnd" below the inserted cells. It worked before - anybody know what I'm doing wrong? Sheets("DRIVER LIST").Select Application.Goto Reference:="RecordEnd" Selection.EntireRow.Hidden = True ...

Copy cells to variable number of rows
I need to copy a formula (that a macro has entered at W3 & X3) down to the last row for that particuliar wk. I've figured out how to get the number of rows for that wk( in this case i=93, i will change for each wk) but can't figure out the syntax for the Range: Selection.Copy Range("W4: ? ").Select ActiveSheet.Paste instead of Selection.Copy Range("W4: ? ").Select ActiveSheet.Paste try range("W4:W"&i).filldown "Acct Supr - DCTC" wrote: > I need to copy a formula (that a macro has entered at W3 & X3) down to the > l...

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...

How do I create a bulleted list text format inside cell?
I am collecting data and want to format the text inside the "notes" (my title) cell so that it can be more easily read. I would like to make a bulleted list. There are several cells that need this formatting. Is this possible? There is no help for this. You have to do it manually. -- Regards, Tom Ogilvy "DEH" <DEH@discussions.microsoft.com> wrote in message news:DFAEE5CC-0AAD-41D7-9DE1-396187DA2681@microsoft.com... > I am collecting data and want to format the text inside the "notes" (my > title) cell so that it can be more easily read. I would...

Question 2:Color cell using conditional formatting
Hi, Using conditional formatting to change color of cell if something is entered works! (thanks Anonnymous!) Now, how woudl I go about to change the color to red if data entered starts with a 1, and change to yellow if if it starts with a letter. I have seen that you can add a 2nd condition in conditional formatting, but I cannot find what formula to use. Or should I use cell value is? Thanks again!! For the 1st condition, use: =LEFT(A1)*1=1 For the 2nd: =OR(AND(CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90),AND(CODE (LEFT(A1))>=97,CODE(LEFT(A1))<=122)) HTH Jason Atlanta, GA ...

Convert hours to minutes within cell?
Is there a way to have a value typed into a cell automatically converted from minutes to hours? Not a formula - at least not in the usual sense - since the formula would be overwritten. Thanks in advance, Mike Hi Mike, Try this Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then With Target If InStr(1, .Text, ":") > 0 Then .Value = .Value * 24 * 60 .NumberFormat = "General" ...

summing adjacent data in a worksheet
I have a column of data and I need to sum the values in adjacent cells when they are bounded before and after by cells that contain a value of 0. Then I need to sum the next group of cells bounded by zeros. So I want a column with subtotals rather than one total. The number of cells in one group may vary. I've tried using the sumif function, but what I want doesn't seem to quite fit. I'm using Excel 2007. So, you have something like this: 1 2 3 0 4 5 6 7 0 0 8 9 0 10 11 12 13 0 0 and you want to get something like this: 1 2 3 6 0 ...

How to Tab over to a specific cell... not the adjacent one?
In order to make a form more user friendly, I would like to allow the user to use the "Tab" key to jump from one input field to another (unlike moving to the adjacent cell). Is this possible? Thanks in advance for your suggestions. Regards, SK Lock all cells> unlock desired cells>lock worksheet>test -- Don Guillett SalesAid Software donaldb@281.com "SK" <sk_nj_junk@yahoo.com> wrote in message news:060101c42bc0$e4c1fc50$7d02280a@phx.gbl... > In order to make a form more user friendly, I would like > to allow the user to use the "Tab" k...

cell value
How do I get a cell value - I want to use the text in P3 for the subject line in an email using .sendmail "address@address", cell P3 text her THANKS Hi bob For more examples visit my website Use a cell containing an email-address like this ..SendMail Sheets("mysheet").Range("a1").Value, _ "This is the Subject line" Use also a cell for the subject like this. ..SendMail Sheets("mysheet").Range("a1").Value, _ Sheets("mysheet").Range("b1").Value -- Regar...

Selecting X cells based on answer to formula
Hi, I am trying to put together a spreadsheet that performs a couple of tasks. The first is basic resourcing which i am ok with, sort of how many heads and how many hours available means X cases will be done. The next thing I need to do is use the X cases result to identify from a list the oldest date and work through sequentially X dates in that column, which will not be consecutive, and tell me what the date of that case is. So if there were 10 cases done I would like one cell saying the oldest case worked on was - first date in column. And following doing the 10 cases I'd lik...

how do i use the data in one cell as the row number of another cel
hello in one of excell sheets i have formula in one cell. in this formula i have a reference to a cell whose row number is the data contained in another cell. how do i extract this data to reference my destination cell. thanking in advance See if this helps: http://www.officearticles.com/excel/indirect_to_switch_worksheets_in_microsoft_excel.htm Your formula would be more like: =INDIRECT("C"&A1) So, if you have a 4 in cell A1, this will return the contents of C4. ************ Anne Troy www.OfficeArticles.com "vidhya" <vidhya@discussions.microsoft.com> wrote ...

Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have no use for all of the rows at the same time and have to keep hiding and showing them as need arises. Is there a macro to hide chunks of it based on the value of one cell of the row? In other words, en each row I will have a formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to hide the row or not. I tried case.select but it takes a LONG time and I would have to write a piece of code for every line. FYI, the rows that need hiding will be in sequence, in other words, fro...

Help with cell totals
Hi all, My problem is that I have two rows, one contains the manufacturers name, and the other the amount that manufacturer supplies. What I need to do is get the sum of the amount supplies for each manufacturer. Example: Company 1 8 Company 2 5 Company 2 7 Company 1 2 Totals: Company 1 10 Company 2 12 -- blackie ------------------------------------------------------------------------ blackie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15223 View this thread: http://www.excelforum.com/showthread.php?thre...

2 Cells on 2 Sheets
Hello! I have a workbook with 2 sheets. Currently - Sheet 1, A1 is a dollar amount entered by the user Sheet 2, B1 references Sheet 1, A1 and is locked. Now - We would like the user to be able to change Sheet 2, B1 and have it update Sheet 1, A1 and vice versa. So that the user has the option of changing the dollar amount in 2 places, rather than on Sheet 1 only. Is this possible? Thanks in advance! Sarah You can do it by using an event macro that looks for changes. But this can easily break if the user doesn't allow macros to run--or even turns off events. I wouldn't use this....

Linking chart parameters to cells
Is anyone aware of a way of linking chart parameters normally set in the various dialog boxes, to a value in a cell. For example, suppose I'd like the value (Y) axis minimum and maximum value to change based on the value of a certain formula, can I somehow link those values to a cell in the spreadsheet. If not, it'd be cool if I could. Maybe the next version of Excel? >-----Original Message----- >Is anyone aware of a way of linking chart parameters >normally set in the various dialog boxes, to a value in a >cell. For example, suppose I'd like the value (Y)...

how do I insert a filename into a formula from another cell?
I have a spreadsheet with a filename in a cell. I would like to reference that filename in a formula. I can't seem to get it to work. "+cell number" doesn't work. Any suggestions? You would normally use INDIRECT to do this, along the lines of: =INDIRECT("["&A1&"]Sheet1!C2") where A1 contains your filename (with the .xls extension) and you are trying to return data from C2 on Sheet1 of that file. However, INDIRECT will only work with files that are open, so you would have to have the file open for this to work. Hope this helps. Pete On...

Conditional format to determine if last character in a cell is a number or a letter
I have a spreadsheet where I enter weights. The weights can be any whole number 1-99999. I would like to be able to flag suspicious weights using conditional formatting by putting a letter as the last character, such as 123a, or 57b, 4471c, etc.What conditional formatting formula can I use to accomplish this? Thanks, Tonso Assuming your weights are in column A, starting with A2, highlight the cells in that column with A2 as the active cell, and use this formula in the CF dialogue box: =3DAND(CODE(UPPER(RIGHT(A2)))>=3D65,CODE(UPPER(RIGHT(A2)))<=3D90) Then click on the Format button a...

Return Range Based on Cell Value
This topic has been covered in varying degrees, but the problem is that I do not want to sum or count the range. I want the range itself returned as the value. For instance in A1 is 7. i.e. July In B1-B12 are dates 201101, 201102, etc. I need a formula that will count out the cells and return the range based on that value in A1. In this case it would return B1:B7. This of course would be nested in another formula. On Tue, 4 Oct 2011 17:18:20 -0700 (PDT), Pablo < > wrote: >This topic has been covered in varying degrees, but the problem is >that I do not want to sum or count the...