| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Range
I have a list of Sales (column A) and a list of closing dates (Column B). On
a separate sheet, I have a summary page showing total sales closing within 30day,then 60days then 90days. I was going to use the Sumif functions =sumif(B1:B1000, criteria, A1:A1000) but I can't figure out the criteria formula for each of the 3 periods. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range
Use something like this:
=SUMIF(B1:B1000,"<="&TODAY()+30,A1:A1000) - SUMIF(B1:B1000,"<"&TODAY (),A1:A1000) The first term sums all the sales up to 30 days away, and the second term (subtracted from this) is all the sales before today. Just change the 30 to 60 or 90 for your other date ranges. Hope this helps. Pete On Aug 21, 1:20*am, JeffK wrote: I have a list of Sales (column A) and a list of closing dates (Column B). *On a separate sheet, I have a summary page showing total sales closing within 30day,then 60days then 90days. I was going to use the Sumif functions =sumif(B1:B1000, criteria, A1:A1000) but I can't figure out the criteria formula for each of the 3 periods. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Range
Suppose your summary sheet has got Start date and end date and in ColC you
need the totoal between..try the below formula Col A Col B Col C 8/21/2009 8/23/2009 = =SUMPRODUCT(--(Sheet1!B1:B1000=A1),--(Sheet1!B1:B1000<=B1),Sheet1!A1:A1000) Sheet1 cols A and B contains the data If this post helps click Yes --------------- Jacob Skaria "Pete_UK" wrote: Use something like this: =SUMIF(B1:B1000,"<="&TODAY()+30,A1:A1000) - SUMIF(B1:B1000,"<"&TODAY (),A1:A1000) The first term sums all the sales up to 30 days away, and the second term (subtracted from this) is all the sales before today. Just change the 30 to 60 or 90 for your other date ranges. Hope this helps. Pete On Aug 21, 1:20 am, JeffK wrote: I have a list of Sales (column A) and a list of closing dates (Column B). On a separate sheet, I have a summary page showing total sales closing within 30day,then 60days then 90days. I was going to use the Sumif functions =sumif(B1:B1000, criteria, A1:A1000) but I can't figure out the criteria formula for each of the 3 periods. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
| Sum If range of dates date range, sum totals | Excel Worksheet Functions | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
