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

Report •

#1
March 3, 2013 at 07:57:27
✔ Best Answer
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 comparison
it needs to be surrounded by quotes. IE: ">0"

MIKE

http://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 totalled

Make 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.

MIKE

http://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 left
as 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 030313
you end up the the Text String 030313

If you have a cell that HAS been formatted as a Date and enter
030313, your going to end up with the Date 12/28/1982.

That's because Excel stores dates as a number
representing 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/1900
and came up with 12/28/1982

Even if the cell is formatted as a Date, use the Slash when
entering the date.

MIKE

http://www.skeptic.com/


Report •

Ask Question