Excel summing question

[OBee]

Dear Friends,

I have daily entries of work hours for 4 months that I need to sum on a
30-day basis. I need these hours added up for the last 30 days, as of today.
I've tried multiple functions such as SUMIF and combination of SUM and IF
without any luck.

Please help me with this problem.

Thanks,
Obee

 

[Rick Rothstein \(MVP - VB\)]

Assuming your dates are in Column A and your hour values are in Column B...

=SUMIF(A1:A200,">="&(TODAY()-30),B1:B200)

You can set the ranges to cover future entries (if there is no date or no
hours, nothing will be added for those data rows).

Rick

 

[OBee]

Dear Rick,

That worked!! Thank you very much. I had tried SUMIF before, but it didn't
work. What does "&" do in the formula you indicated below? That's the only
thing I was not trying previously.

Sincerely,
Obee

 

[Rick Rothstein \(MVP - VB\)]

The & is how you concatenate (join) text together... Excel evaluates
(TODAY()-30) as a number, converts it to text and joins it with the ">="
comparison operator. To see it in its most basic form, put the work Hello in
A1 (put a space after the "o" and put the word There in B1, then put =A1&B1
in C1.

Rick

 

[OBee]

Dear Rick,

I was experimenting with data using your equation below and came across this
problem: if someone by mistake inputs work hours for the next few days, then
this equation sums those hours up as well. How can I ensure the equation
sums up the hrs from "today" to "today-30 days" ONLY, and not the hrs beyond
"today" as well?

Thanks,
obee

 

[Mike Fogleman]

Subtract any totals that are >Today...

=SUMIF(A1:A200,">="&(TODAY()-30),B1:B200)-SUMIF(A1:A200,">"&(TODAY()),B1:B200)

Mike F

 

[Rick Rothstein \(MVP - VB\)]

You could just subtract anything greater than today...

=SUMIF(A1:A200,">="&(TODAY()-30),B1:B200)-SUMIF(A1:A200,">"&(TODAY()),B1:B200)

or you could use this alternative function...

=SUMPRODUCT((A1:A99>=TODAY()-30)*(A1:A99<=TODAY())*B1:B99)

I'm not 100% sure, but I think the first formula is more efficient (even
though it involves an extra function call).

Rick

 

[Rick Rothstein \(MVP - VB\)]

And just to have some fun on a weekend, I developed this even shorter
formula...

=SUMPRODUCT((ABS(TODAY()-15-A1:A200)<=15)*B1:B200)

although I have no idea how it fits in efficiency-wise compared to the other
formulas (my 'gut' tells me the two SUMIFs are probably still the fastest).

Rick

 

[OBee]

Hi Rick,

The two sumifs worked like a charm. I haven't tried the others, but I'll
give them a try. Thanks a lot for your help!
Sincerely,
Obee