Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing every other entry in excel | Excel Worksheet Functions | |||
Excel Conditional Summing | Excel Worksheet Functions | |||
Summing columns in Excel? | Excel Worksheet Functions | |||
Summing cells in Excel | Excel Worksheet Functions | |||
Summing cells in Excel | Excel Worksheet Functions |