sum

andy

hello there,
i need your help in sum fonction.
i have i column with date and value but i want to sum just the value.
any help please?

Hi,
When you say values do you mean numbers? If so the problem you will have is that Excel stores dates as serial values which are very difficult to differentiate from normal numbers.
Dom

yes i mean numbers

I'm guessing that he wants a SUMIF - to sum all the values over a particular date. Is this so ? Thanks

Kaps

no i have like that.
5829-9-200825-9-200824-9-200830-9-200825-09-20085018-09-200825-09-200803-09-200818-09-2008

This will only work if all your values are less than 39448 (the serial equivalent of 1/1/2008) and all your dates are greater than or equal to 1/1/2008:
=SUMPRODUCT(($A$1A$11
Other than that you will be looking at a VBA user defined function.
Dom

If all your (non-date) values are less than 39448 (1/1/2008) as Dom suggests, then you could still use SUMIF:

SUMIF #160;ABC158#160;108229/09/2008#160;#160;325/09/2008#160;#160;424/09/2008#160;#160;530/09/2008#160;#160;625/09/2008#160;#160;750#160;#160;818/09/2008#160;#160;925/09/2008#160;#160;1003/09/2008#160;#160;1118/09/2008#160;#160;Spreadsheet FormulasCellFormulaC1=SUMIF#40;A1#58;A11,#34;#60;39448#34;#41; Excel tables to the web #62;#62;  Excel Jeanie HTML 4

Hello,

or you could use

=SUMPRODUCT(--(LEN(A1:A3)

if the values are less than 10000.

it works thank you very much but why  ot should be
thanks again for you all

If you type 1/1/2008 into a cell and then reformat the cell as general you will see that it changes to 39448 which is the serial date value for 1/1/2008. Using the default 1900 date system excel starts with 1 being 1/1/1900 and counts upwards from there. Hours and minutes are stored as fractions of a whole number. It therefore makes telling the difference between dates and values very difficult.
Dom