Thread: Sum depending on date range

Alright, after about 6 hours trying various formulae in Excel 2003, I've given up. I'm attaching the sanitized sheet. I need to sum the minutes in column E, within monthly date ranges. I've tried SUMPRODUCT, SUMIF, SUM(IF), IF, AND, VLOOKUP, and every other permutation I can find. When I use:

=SUMIF(A:A,"<=9/30/2008",E:E)

I get the proper answer of ":15" but if I try anything else, to denote the beginning date, as in

=SUMIF(A:A,"<=9/30/2008",E:E)+SUMIF(A:A,">=9/1/2008",E:E)

I get errors. I've tried so many things I've forgotten which errors go with which tries.

So, in column G, I'd like to be able to subtotal the accumulated time (from column E) since the last time it was billed (or maybe every month, or whenever). I can't just do a SUM and highlight the intervening rows in case a late entry with a past-date gets added, resulting in a fresh date sort, or is I'm asked how many hours I put in from [date] to [date].

It seems like it should be a simple search; it's been anything but. Advice?

Stephen

In the attached file, enter start and end dates in M2 and M3.
Formula in H4 works.
hope this helps.
modytrane.

Originally Posted by modytrane

In the attached file, enter start and end dates in M2 and M3.
Formula in H4 works.
hope this helps.
modytrane.

So why do you figure it doesn't work when I replace your reference to the date cell with the date itself?

Modytrane, I originally attached a slightly different file than what I meant to. Take a look at this attachment and you'll see what I meant by "column G" in the first post. Your solution is an easy one and I thank you for it.

Modytrane, again thank you. The solution isn't exactly what I'm looking for. Using the updated file I posted, I really need a way to find that sum of expended time, post it in Column G, and have it stay there. Using your solution, every time I enter a new start and end date, all the subtotals in Column G change to whatever the new date range is. I don't mind re-entering the formula each time I need to do a new subtotal, but then I need to change the cell address for the start and ent dates each time, also, if I've figured it correctly.

I am confused.
Please create a dummy worksheet showing what you expect as the end result.
In the last sheet you posted, column G "Time Subtotal" is blank. Do you expect that column to have different values in G2:G24?
If so, how would G2 be different from G3? Based on different dates? Where would the dates come from?

It seems like you need to rearrange the sheet, so you can reference different start and end dates and have results tabulated for each pair.

Code:

I don't mind re-entering the formula each time I need to do a new subtotal, but then I need to change the cell address for the start and ent dates each time, also, if I've figured it correctly.

What do you mean by re-entering the formula?

Does the attached format work for you?

modytrane