# Sum skipping non numbers

#### choks90233

##### New Member
I have a table with columns that i need to sum. the problem is there are dates in some of the cells for inspections that are not yet complete. I want to sum the columns and skip the cells that have dates or return the dates as a zero. any help would be greatly appreciated.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Dates are also numbers, so it's not clear how your data makes the distinction between date numbers and non-date numbers. Could you post a small sample to show how your data looks like?

 2 3 1 2 1-Feb 2 Jan-00 1 1 2 Jan-00 0 Feb-00 3 0 0 0 0 0 0 0 0 0 1 0 2 1 1 0 2 0 1 0 1 5 21

<tbody>
</tbody>

this is what i would want it to do

The most simplest thing would be to apply CELL() function in 3rd column (Assumed Column C).
=CELL(choose formats, reference). Format function will return Data type... For date it would return "D" and in general return "G"

After that, apply =SUMIF(C1:C17,"G",A1:A17)
You will get the desired result....
Moreover, I am trying to club these two formulas into 1... Will post soon.

can you elaborate on the =CELL() function? I can't get it to function the way you are describing. it returns a #NAME ? error.

Ok so i have it working now but it is kicking back a V for the dates when it should be kicking back D2 or D3. I cant run the SUMIF when the numbers are not distiguished by the CELL() function

I cannot delete or edit these. But i have it working. Cell()function in a seperate column the SUMIF() based off that third column. Thank you so much for the help. Im sorry I'm a bit slow with all of this.

You are welcome.

L
Replies
9
Views
488
Replies
1
Views
366
Replies
3
Views
969
Replies
1
Views
214
Replies
12
Views
497

1,196,078
Messages
6,013,302
Members
441,760
Latest member
Sharina

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back