Sum column values that contains text &/or dates

Column D3 thru D43 contain a mixture of values & dates.  For example:

D3 = 16,026.76
D4 = 11-18-05
D5 = blank
D6 = 6,855.34
D7= 11-03-05
D8 = blank
D9 = 5,270.00
D10 = 11-02-05

Need sum to equal 28,152.10 (total only values from entry or formulas)

Thanks so much.  mikeburg


-- 
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=489114

0
11/29/2005 4:49:28 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
451 Views

Similar Articles

[PageSpeed] 5

If your dates are text strings which say "11-18-05" for example, then the 
solution below should work.  If they're serial numbers formatted as you show 
then, then the problem is trickier....

The following array formula work do the trick.  You need to hold down the 
control and shift keys while hitting Enter, in order to have excel recognize 
it as an array.
{=SUM(IF(ISNUMBER(D3:D43),D3:D43))}

Don't know how well you know array formula.... don't actually type the {} 
signs, those get inserted when you enter the formula holding down shift and 
control.

"mikeburg" wrote:

> 
> Column D3 thru D43 contain a mixture of values & dates.  For example:
> 
> D3 = 16,026.76
> D4 = 11-18-05
> D5 = blank
> D6 = 6,855.34
> D7= 11-03-05
> D8 = blank
> D9 = 5,270.00
> D10 = 11-02-05
> 
> Need sum to equal 28,152.10 (total only values from entry or formulas)
> 
> Thanks so much.  mikeburg
> 
> 
> -- 
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=489114
> 
> 
0
EddieO (39)
11/29/2005 5:21:03 PM
Mike,

There may be a single formula to do this but you could use a helper
column in column E to get the answer.  I assume that all of your
numbers are formatted the same.  Use the CELL function and SUMIF.  

In Column E3:E10

=CELL("format",D3), drag down to E10

This identifies a numeric value using the thousands separator as ",2"
and dates as D4.

Then use this in the cell you want the calculation done in.

{=SUM(IF($E$3:$E$10=",2",$D$3:$D$10,0))}

Commit with Ctrl-Shift-Enter as it is an array formula.

Cheers,

Steve


-- 
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=489114

0
11/29/2005 5:25:58 PM
mikeburg,
the SUM() function will disregard text. It will not distinguish between
dates and numbers because dates are internally stored as numbers. Thus,
if you only want the sum of purely numeric values you will need to use
VBA and a helper column.

Function IsItANumber(x)
IsItANumber = isnumeric(x)
End Function

To define this function, Alt+F11 to go to the VBA editor, Insert|Module
and paste the function code above.

Back in Excel, in another column (say E3:E43), use =IsItANumber(D3) and
copy down to row 43. You can then use the following formula:
=SUMIF(E3:E43, TRUE, D3:D43)

HTH
Kostis Vezerides

0
vezerid (64)
11/29/2005 5:28:51 PM
I would use a helper column of cells:

=IF(AND(ISNUMBER(D3),LEFT(CELL("format",D3),1)="D"),"",D3)
and drag down

Then sum that column.

Really, I'd separate my data into different columns.  It could be a mess if
someone formats the whole column incorrectly.



mikeburg wrote:
> 
> Column D3 thru D43 contain a mixture of values & dates.  For example:
> 
> D3 = 16,026.76
> D4 = 11-18-05
> D5 = blank
> D6 = 6,855.34
> D7= 11-03-05
> D8 = blank
> D9 = 5,270.00
> D10 = 11-02-05
> 
> Need sum to equal 28,152.10 (total only values from entry or formulas)
> 
> Thanks so much.  mikeburg
> 
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=489114

-- 

Dave Peterson
0
petersod (12004)
11/29/2005 5:46:18 PM
Thanks for all your great help.  mikeburg


-- 
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581
View this thread: http://www.excelforum.com/showthread.php?threadid=489114

0
12/2/2005 12:27:31 AM
Reply:

Similar Artilces:

Changing Function to use a Date Input
Hi. can anyone please advise me on how to change the function keys.. sa F10, F11 & F12 to use to input a date into a cell. EG. F10 to be 01/01/04 F11 to be 02/01/04 F12 to be 03/01/04 What I require is to go to any cell and press the relevant function ke to Input the associated date Any help would be greatly appreciate Many thank Rob PS using Excel 200 -- Message posted from http://www.ExcelForum.com First, I'm not sure if you entered Jan 1, 2004, Feb 1, 2004, and Mar 1, 2004 or Jan 1st-3rd, 2004. Here are a couple of macros. The first turns this on, the second turns it...

change the text in the legend of a chart w/o changing pivot text
I am trying to create a series of pie charts that compares demographic data for clients assisted with multiple housing-related activities (new homebuyer, rehab, foreclosure prevention, etc.) If I want to look at the age of new homebuyers, on my pivot table I deselect all activities except new homebuyer. After I make my pie chart, in the legend the labels are "new homebuyer 18-24", "new homebuyer 25-34", etc. It's pretty self-explanatory that these are all "new homebuyers". I need for the New Homebuyer part to not be shown in the legend, but I can...

Text Form Fields
Not sure if I have posted this in the right area, but here goes. I am more familiar with excel so working with forms in word is new to me. I have been tasked with developing a number of forms at work in Word 2003 for posting on our website. A guy who left, and is now not contactable, developed a whole range of forms before he left. He made sure the forms could be filled in online by inserting text editable fields for client use (both the 'greyed out' section and dotted lines can be seen when viewing and completing online and the dotted lines auto-delete as text is inserte...

Current time for data entered in column
Whenever data is entered in a cell, current time should come auto i another corresponding cell. For example, time should automaticall come in Y column for any data entered in any cell of the column A. Fo A10 time should come in Y10 and for A12 it should come in Y12 and s on. I need it for doing time and motion study. Appreciate all help -- Message posted from http://www.ExcelForum.com Hi Mohitmahajan! Based on JE McGimpsey (http://www.mcgimpsey.com/excel/timestamp.html) Let's say that every time an entry is made in cells A2:A100, the corresponding cell in column Y should have the...

Conditional Formatting of Text...
....I know how to apply Conditional Formatting to cells containing numeric data; is it possible to apply it to cells where the result is textual? For example: =if(a1>=5000,"High","Low") In this instance, I would want to conditionally format this cell so that if the result is High, then the text should be bold and red. Thanks in advance. I'm using Excel 2002... Hi click on the cell choose format / conditional formatting choose cell value is equal to ="high" click the format button set your formatting click OK twice Regards JulieD "Birmangirl&quo...

Changing Default DURATION value of 30 min. in activities
Is there a way to change the 30 min. default value in activity types? Even if through the database? Thanks, Oliver The Duration field is represented as a picklist on the activity form. Picklist values can usually be customised through the CRM user interface which would allow you to change the default and add additional values. However, at present the CRM Activity form is not customisable through any supported method. Maybe in a future release. ...

i need to have more text options in a running writting font
I have to format a logo from a hand written sign. The text "Script MT Bold" is quite acceptable but the 'f' needs to have a loop on the bottom not on the top. The dot above the 'i' needs to be round not square. Is there any way I can develop the text that I need or change the actual letters? I have done the work in 2003 Publisher. I use Brush Script MT and the letters are properly portrayed. The loop for the "f" is on the bottom and the dot over the "i" looks rounds. If I can be of help, send me a note to: dschmidt AT pacifier DOT com -...

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

text to fit in cell
How do I make the text fit in a cell. I want to be able to print the page with all of the words in the cell even if it has to make the cell larger, without going over into the next cell. How do I do this? Hi goto 'Format - Cells - alignment' and check 'Wrap text' -- Regards Frank Kabel Frankfurt, Germany DaveB wrote: > How do I make the text fit in a cell. I want to be able > to print the page with all of the words in the cell even > if it has to make the cell larger, without going over > into the next cell. How do I do this? Dave Format>Cells>Alignm...

conditional formatting #value!
Hi Is it possible to create a conditional formatting condition that can detect the condition of #VALUE! ? That is NOT the text string but the "error condition". What I am trying to highlight is that certain calculated values have not been correctly calculated on the sheet and need further investigation. Thanks Clive Hi Clive, Use the 'Formula Is' option on the conditional formating dialog and enter the following, assuming the cells is A1. =ERROR.TYPE(A1)=3 You can also use the Go To dialog (CTRL+G), Special... to locate cells with Errors. Cheers Andy Clive Long...

Filtering out text with conditional formatting
hi there, I have a problem I need help with. I have a worksheet that has column with 13 digit long number in it. I want a formula (or is it conditional formatting) that will highligh the cell if the last 5 digits of the number are not �00000� The number is in text format and needs to stay that way� so I wa thinking there could be come way to examine the text .. I dunno� an ideas greatly appreciated� thanks -- bluebea ----------------------------------------------------------------------- bluebean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2595 View th...

How do I change headings for columns from numbers to letters? #2
...

Winmail.Dat Being Created On Plain Text Emails
I am running Outlook 2003 with all the updates and I am having a winmail.dat file created on every email that I send even the ones in Plain Text. Are there other settings that I need to make in Outlook to stop this from happening? Thanks. Rick Bellefond RB Data Services www.rbdata.com Try turning off Word as your e-mail editor to see if it stops. (See Tool > Options > Mail Format tab) If Word is off, go to Tools > Options > Mail Format tab > Internet Options. Make sure the dropdown in the middle of the dialog is not set to Outlook Rich Text. Check the e-mail address...

How do I prevent Excel from auto-correcting the date format?
I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects it to Feb-1. How do I prevent it from doing this? One way is to format the input column / range as Text first (via Format > Cells > Number tab > Text > OK) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Watts" <Watts@discussions.microsoft.com> wrote in message news:16725A60-590A-4658-857A-2E6C8420D253@microsoft.com... > I am using Excel, and every time I enter 2/1 in the spreadsheet - it corrects > it to Feb-1. How do I prevent it from do...

cells changing from number to date
I am putting a number in a cell, then tabbing to the next cell, the previous cell changed from a number to a date. How do I keep the number there? What are you inputting, a fraction?if so, format as # ??/?? -- HTH RP (remove nothere from the email address if mailing direct) "mwhite@srgmac.com" <mwhite@srgmac.com@discussions.microsoft.com> wrote in message news:E71CED6D-8370-44D1-A4BF-9A6B352205F3@microsoft.com... > I am putting a number in a cell, then tabbing to the next cell, the previous > cell changed from a number to a date. How do I keep the number there? ...

can't copy text to new pub document
I am trying to copy entire pages from one Publisher 2007 doc to another. It will copy the text in some text boxes but not in others. I don't understand why this is happening. How do I get it to copy the entire page as is? I am using Ctrl A or Select All. Thank you. Oh, another thing to add: Even if I select only a single text box, it will not copy any text. "lindalou" wrote: > I am trying to copy entire pages from one Publisher 2007 doc to another. It > will copy the text in some text boxes but not in others. I don't understand > why this is happening. How d...

Enter Parametr Value
I have the following SQL that selects the TOP 10 clients by percentage of sales. I want to be able to SELECT the TOP 20, TOP 40, TOP 60, etc.. by entering a value when the query runs. How might I change my SQL? Any help appreciated. SELECT TOP 10 tblPct.ip, tblPct.Pct FROM tblPct ORDER BY tblPct.Pct DESC; On Fri, 8 Jan 2010 17:52:02 -0800, NEWER USER <NEWERUSER@discussions.microsoft.com> wrote: The only way to do that is to use dynamic sql. This will not work: select top forms!myForm1myControl from myTable This will not work: select top [parMyTop] from myTable (...

Match name, value & identify errors
Thanks in advance, Data: ------data 1------ --------data 2------ col A col B col C col D yum 180 yum 180 yum 200 yum 630 yum 430 yum 832 yum 330 Objective: Match data 1 to data 2 and identify errors Roll 1 - no problems, both names and value matches Roll 2 & 3 matches of Data 1 matches to roll 2 of data 2 (or vice versa and it could hv 3 values in matches to ...

custom date format 04-12-10
I can use the built-in formats in XL2003 to get a date like " Monday, September 27, 2010". Is it possible to create a custom format to give a date like "Mon, Sept 27, 2010"? Thanks so much for all the help, past, present, and future!! Tonso How about Mon, Sep 27, 2010 If that's ok, try: ddd, mmm dd, yyyy or ddd, mmm d, yyyy Tonso wrote: > > I can use the built-in formats in XL2003 to get a date like " Monday, > September 27, 2010". Is it possible to create a custom format to give > a date like "Mon, Sept 27, 201...

combination stacked column and line data
Help. I've been asked to create a chart that has two sets of data that are stacked (columns) and 5 sets of data that display in line format. Is this an option in Excel. I can do a columan and a line but can't seem to add more than that. I'm using Excel 97 (but have access to Excel 2003). Please and thanks. Don't spend a lot of time looking for every last permutation in the "built in custom" chart types. What you probably want isn't there, but it's easy to roll your own type. Make a chart with all data plotted in one format (probably line, so you ha...

I have accidently hidden all my drwing, text boxes, etc
I have a file with several drawings, text boxes, etc. I now cannot see them nor can i add any new drawings, text boxes, etc., to this file. I might have accidently hit some shortcut key sequence to hide them. How do i get them back? Thank you, Tonso On Jun 20, 10:32 am, Billy <wthoma...@hotmail.com> wrote: > I have a file with several drawings, text boxes, etc. I now cannot see > them nor can i add any new drawings, text boxes, etc., to this file. I > might have accidently hit some shortcut key sequence to hide them. How > do i get them back? > > Thank you, > >...

Know of a shortcut for Paste Special Values?
Anybody know of a shortcut for Paste Special Values? You can add the Paste Values button to the toolbar: Choose Tools>Customize Click on the Commands tab Select the Edit category Scroll down the list of commands to find Paste Values Drag the Paste Values command to an existing toolbar Click Close Nanne wrote: > Anybody know of a shortcut for Paste Special Values? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Hide Alternate Labels on Date Axis
Hi, I'm having trouble trying to hide every other label along a horizontal date axis. I start on the 01-Jan up to 10-Jan and through using the 'Format Axis'->'Scale'->'Major Unit' and setting this to 2 I can hide the labels: '02-Jan'; '04-Jan'; etc. However, I want to hide the odd dates: '01-Jan'; '03-Jan'; etc. I have tried the various combinations with the dialog box but can't get anything to work. Should I be using a custom formula or something? Any help is much appreciated. Paul. In article <1168177092.892944...

sorting two columns of merged cells
I have two columns of merged cells. two cells in each column are merged in each row: ie A1 and B1 are merged into one cell, a2 and B2, etc. The next column is the same; C1 and D1 are merged, C2 and D2, etc. Is there any way I can sort these columns? I need a descending sort by col A and B. Im using Excel 2003 Hope this is clear. Jim Don't merge cells. Look in the archives of this group for countless reasons why not; you've just found one of them. -- David Biddulph "bigjim" <bigjim@discussions.microsoft.com> wrote in message news:...