# Solved Excel If Statements using cell format

February 20, 2014 at 15:41:54
Specs: Windows 8
 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

See More: Excel If Statements using cell format

#1
February 20, 2014 at 16:10:34
 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.

Report •

#2
February 20, 2014 at 18:01:38
 Here is another way, but you will need a Helper column 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/2014 ```In 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/yyYour 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 0 ```Now in cell C1 enter the formula: =SUMIF(B1:B10,"=X",A1:A10)See how that works for you.MIKEhttp://www.skeptic.com/
 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 ```