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

Report •

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:


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.

Report •

February 20, 2014 at 18:01:38
✔ Best Answer
Here is another way, but you will need a Helper column and we are using the CELL() function.

With your data like:

 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 rows

Your 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     0

Now in cell C1 enter the formula: =SUMIF(B1:B10,"=X",A1:A10)

See how that works for you.


Report •

February 20, 2014 at 18:21:10
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:


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

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

Report •
Related Solutions

Ask Question