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"
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 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.
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 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 030313If 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.
Ad Choices