Sum skipping non numbers

choks90233

New Member
Joined
Jan 27, 2019
Messages
11
Office Version
  1. 2010
Platform
  1. Windows
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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?
 
Upvote 0
23
12
1-Feb2
Jan-001
12
Jan-000
Feb-003
00
00
00
00
01
02
11
02
01
01
521

<tbody>
</tbody>


this is what i would want it to do
 
Upvote 0
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.
 
Upvote 0
can you elaborate on the =CELL() function? I can't get it to function the way you are describing. it returns a #NAME ? error.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top