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.

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

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

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,

andnota 5 digit number,

then the one that looks like a Date is really TEXT.MIKE

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?

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.

Why do some cells default back to text when the date is typed in, even when the cell is formatted as a date?Dates

requirethe slash character to be used

for Excel to recognize them ascorrectDates.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 theText String030313If 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/1982Even if the cell is formatted as a Date, use the Slash when

entering the date.MIKE

Ask Your Question

Weekly Poll