# Solved excel formula for sum if date

Microsoft Office 2007 home and student
March 3, 2013 at 07:39:03
Specs: Windows Vista
 I am trying to create a formula that will add a range of cells if there is a date (any date) in the corresponding cells.In column C(rows 4 to 1334) there are counts entered. In column H, there are dates entered if the corresponding counts have been received. I want a formula at the bottom of the page that will add up the counts in column C, but only if there is a date in the corresponding cell in column H (cell is no longer empty).I'm pretty sure it's a sum if formula I need, but the formula below does not work. So, I'm pretty sure I'm missing something obvious.=sumif(H4:1334,>0,C4:C1334)I would be very appreciative of any help you could offer.Thank you.

See More: excel formula for sum if date

#1
March 3, 2013 at 07:57:27
 Not quite right, try this:=SUMIF(H4:H1334,">0",C4:C1334)You left off the column indicator in the first range and in the criteria to match section, if your doing a comparisonit needs to be surrounded by quotes. IE: ">0"MIKEhttp://www.skeptic.com/

Report •

#2
March 3, 2013 at 08:44:10
 The formula is still not capturing all of the amounts needing to be totalled. I have column C formatted as number cells with no decimal places. Column H is formatted as a date (mm/dd/yyyy).

Report •

#3
March 3, 2013 at 08:54:14
 The formula is still not capturing all of the amounts needing to be totalledMake sure all your Dates are really Dates, not TEXT that look like dates.A quick way, is to format your H column as Number,if you see a cell with a Date in it, and not a 5 digit number, then the one that looks like a Date is really TEXT.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
March 3, 2013 at 09:19:58
 That was the problem! Thank you so much. No wonder none of the formulas I had been trying would work.Why do some cells default back to text when the date is typed in, even when the cell is formatted as a date? Is there a way to make sure this does not happen?

Report •

#5
March 3, 2013 at 09:44:53
 Disregard the last message. It appears some had been entered as mm/dd/yyyy while others had been entered as dd/mm/yyyy. Correcting this solved the problem of why it wouldn't keep the date format.Thank you for all of your help.

Report •

#6
March 3, 2013 at 09:44:56
 Why do some cells default back to text when the date is typed in, even when the cell is formatted as a date?Dates require the slash character to be used for Excel to recognize them as correct Dates.If you have a cell that has NOT been formatted as a Date but leftas the default General format and enter 030313, your going to end up with the number 30313.If you have a cell that has been formatted as TEXT and enter 030313you end up the the Text String 030313If you have a cell that HAS been formatted as a Date and enter030313, your going to end up with the Date 12/28/1982.That's because Excel stores dates as a numberrepresenting the number of days since 01/01/1900.With out the Slash, Excel converted 030313, to 30313, (dropped the leading zero) then converted that to the number of days since 01/01/1900and came up with 12/28/1982Even if the cell is formatted as a Date, use the Slash when entering the date.MIKEhttp://www.skeptic.com/

Report •