Hi, I'm trying to figure out how to do a SUMIF/S formula that only adds cells that are numbers but not cells that are dates. Is there any way to do this? Thanks

Not knowing what your numbers and/or dates look like, it's hard to give a specific answer. For example, if all of your dates were equal to or later than 1/1/2014 and all of your numbers were less than 41640, you could use this:

=SUMIF(A1:A10,"<41640")

Since 1/1/2014 is stored internally as 41640, it can be treated just like any other number as far as the SUMIF function is concerned.

If we had some specifics about your data, perhaps we could offer something to fit your exact needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here is another way, but you will need a Helpercolumn and we are using the CELL() function.With your data like:

A 1) 02/20/2014 2) 1 3) 2 4) 02/20/2014 5) 3 6) 02/20/2014 7) 4 8) 02/20/2014 9) 5 10) 02/20/2014In Cell B1 enter the formula: =IF(CELL("format",A1)="D4",,"X")

Drag down 10 rowsYour Dates must be in the US standard mm/dd/yyyy or mm/dd/yy

Your data should now look like:

A B 1) 02/20/2014 0 2) 1 X 3) 2 X 4) 02/20/2014 0 5) 3 X 6) 02/20/2014 0 7) 4 X 8) 02/20/2014 0 9) 5 X 10) 02/20/2014 0Now in cell C1 enter the formula: =SUMIF(B1:B10,"=X",A1:A10)

See how that works for you.

MIKE

Another option is with a User Defined Function (UDF) but it would help to know the exact format of the dates in question. e.g. This UDF will sum any cells in the selected Range that do not have the NumberFormat specified. If more than one date format is used, an OR could be added to handle multiple formats.

Place the code in a Standard Module and you can use this in any cell:

=SumNoDate(A1:A10)

Function SumNoDate(ByVal Mycells As Range) Application.Volatile For Each cell In Mycells If Not cell.NumberFormat = "m/d/yyyy" Then tempSum = tempSum + cell End If Next SumNoDate = tempSum End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History