| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF within date range as a function of today()'s date
Column A = Each day's date for this year
Column Q = Each day's production goal. I am trying to come up with a cell that will look at today's date and sum the production goal figures between today and the 1st of the month. I'm thinking I need to summarize the production goals prior to the 1st, then subtract that figure from the YTD figure. Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
For the total to date this month try:
=SUM((MONTH(A1:A1000)=MONTH(TODAY()))*Q1:Q1000) This is an array formula so it must be entered with Crtl + Shift + Enter not just a simple Enter -- HTH Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "irvine79" wrote in message ... Column A = Each day's date for this year Column Q = Each day's production goal. I am trying to come up with a cell that will look at today's date and sum the production goal figures between today and the 1st of the month. I'm thinking I need to summarize the production goals prior to the 1st, then subtract that figure from the YTD figure. Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
"irvine79" wrote: Column A = Each day's date for this year Column Q = Each day's production goal. I am trying to come up with a cell that will look at today's date and sum the production goal figures between today and the 1st of the month. I'm thinking I need to summarize the production goals prior to the 1st, then subtract that figure from the YTD figure. Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
Figured it out, I just had to subtract the cummulative annual figure prior to
the first of the month from the total annual figure to date. the formula: =SUMIF(A3:A262,"<"&TODAY(),Q3:Q262)-SUMIF(A3:A262,"<"&DATE(YEAR(TODAY()),MONTH(TODAY() ),1),Q3:Q262) "irvine79" wrote: Column A = Each day's date for this year Column Q = Each day's production goal. I am trying to come up with a cell that will look at today's date and sum the production goal figures between today and the 1st of the month. I'm thinking I need to summarize the production goals prior to the 1st, then subtract that figure from the YTD figure. Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
You've basically got it right... something like
=sumif(a:a,"<="&today(),q:q)-sumif(a:a,"<"&(today()-day(today()+1),q:q). The logic is add up all the goals for today or earlier, and subtract all the goals from prior to this month. today()-day(today()) gets you the last day of the prior month, so adding one gets you the first day of this month. If you're interested in a particular date other than today, just change today() in the formula to a cell containing the date of interest. "irvine79" wrote: Column A = Each day's date for this year Column Q = Each day's production goal. I am trying to come up with a cell that will look at today's date and sum the production goal figures between today and the 1st of the month. I'm thinking I need to summarize the production goals prior to the 1st, then subtract that figure from the YTD figure. Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
....or you could do the same with SUMPRODUCT.... =SUMPRODUCT(--(A3:A262<=TODAY()),--(A3:A262TODAY()-DAY(TODAY())),Q3:Q262) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=568750 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
Hi I have to give a salry increase for 'X'person Cell A - Amount to be given I have to distribute this amount in 1. Basic 2. HRA (calculated@25%basic) 3. Provident Fund (calculated at 12% basic) Please provide a formula -- ashley_miranda ------------------------------------------------------------------------ ashley_miranda's Profile: http://www.excelforum.com/member.php...o&userid=37155 View this thread: http://www.excelforum.com/showthread...hreadid=568750 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF within date range as a function of today()'s date
Hi Ashley, normally you'd start a new thread for an unrealted query..... can you give an example? What is basic equal to? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=568750 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MAX figure within a date range as a function of today()'s date | Excel Worksheet Functions | |||
| How to use Date range function in Sum(if....) | Excel Discussion (Misc queries) | |||
| date function | Excel Discussion (Misc queries) | |||
| Match function...random search? | Excel Worksheet Functions | |||
| Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Linear Mode
