Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=489114 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum column values that contains text &/or dates
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...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=489114 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum column values that contains text &/or dates
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...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=489114 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum column values that contains text &/or dates
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum column values that contains text &/or dates
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...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=489114 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum column values that contains text &/or dates
Thanks for all your great help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=489114 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column | Excel Discussion (Misc queries) | |||
Match formula that pulls unique values from another column? | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
another text to column problem | Excel Worksheet Functions |