Ignoring Dates in Sums

[Guest]

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!

 

[Pete_UK]

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

 

[vezerid]

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

 

[Guest]

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!

 

[Guest]

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!