Excel summing question
 

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

Excel summing question
 

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" wrote in message
...
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

Excel summing question
 

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)" wrote:

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" wrote in message
...
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

Excel summing question
 

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" wrote in message
...
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)" wrote:

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" wrote in message
...
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

It's summing but forward in time as well...
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

It's summing but forward in time as well...
 

Subtract any totals that are Today...

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

Mike F
"OBee" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

It's summing but forward in time as well...
 

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


"OBee" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

It's summing but forward in time as well...
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"OBee" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

It's summing but forward in time as well...
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"OBee" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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