Sum skipping non numbers

[choks90233]

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.

 

[Aladin Akyurek]

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?

 

[choks90233]

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

 

[mail2master]

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.

 

[choks90233]

ADVERTISEMENT

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

 

[choks90233]

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

 

[choks90233]

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.

 

[mail2master]

You are welcome.