Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Dates in Sums
I have a very lengthy column which includes data of various sorts: some cells
are textual, some have dates, and others are numbers. I need to add the values in only the cells that have numbers. The SUM formula disregards text, so those cells don't pose a problem, but the date cells skew my results if I use some (since they get included in their converted representation). Is there an easy way to add only numerical (ie, non-date and non-textual) values? On a similar note: Is there a quick way to count the cells that have text in them? I don't need a sum in this case: Just a count of the cells. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Dates in Sums
The problem is that to Excel a date is just a number, so how could you
exclude one number and not another? Numbers that represent recent dates are all in the 38,000 range (eg today is 38811), so if the other numbers are very much lower than this, then you could make use of this to exclude the dates, eg something like: =SUMIF(A1:A1000,"<"35000,A1:A1000) if the column with the mixed data is A. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Dates in Sums
Thanks! The two replies I've gotten have clarified things quite a bit--though
working with dates in Excel always strikes me as a bit complex. Thanks again! "Pete_UK" wrote: The problem is that to Excel a date is just a number, so how could you exclude one number and not another? Numbers that represent recent dates are all in the 38,000 range (eg today is 38811), so if the other numbers are very much lower than this, then you could make use of this to exclude the dates, eg something like: =SUMIF(A1:A1000,"<"35000,A1:A1000) if the column with the mixed data is A. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Dates in Sums
Cells formatted as date can be identified with the CELL(ref, "format")
formula. FOr example, if cell A2 contains 3-mar-06, then =CELL(A2,"format") will return "D1". All date format codes start with "D". Hence you can use something like: =SUMPRODUCT(A2:A100,--(LEFT(CELL(A2:A100,"format"),1)<"D")) Does this help? Kostis Vezerides |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring Dates in Sums
Between your reply and the other one just above, I'm on the right track. It's
actually kind of a relief that there wasn't some incredibly obvious solution I was simply too stupid to know. Thanks! "vezerid" wrote: Cells formatted as date can be identified with the CELL(ref, "format") formula. FOr example, if cell A2 contains 3-mar-06, then =CELL(A2,"format") will return "D1". All date format codes start with "D". Hence you can use something like: =SUMPRODUCT(A2:A100,--(LEFT(CELL(A2:A100,"format"),1)<"D")) Does this help? Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
US dates to UK?? | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |