Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum based on ranges pivot table?
I have a column that contains dates & quantities I would like to sum based on
date ranges that I would define. For example I would want the sum quantities whose dates fall in-between Nov. 11 & Nov 17. Nov. 18 & 24€¦.. So on & so forth. I have been trying to figure out a way to do this with maybe a v lookup or pivot table. However I have not been able to do so. If anyone has any ideas as to how I can do what I want to do I would be very grateful. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum based on ranges pivot table?
The usual way of getting a range of dates is to use a subtraction
(dates <= Nov 17) - (dates < Nov 11) You can use a two SUMIF function to get the results. =sumif(B1:B100,"<="&11/17/07,C1:C100)-sumif(B1:B100,"<"&11/11/07,C1:C100) "DP7" wrote: I have a column that contains dates & quantities I would like to sum based on date ranges that I would define. For example I would want the sum quantities whose dates fall in-between Nov. 11 & Nov 17. Nov. 18 & 24€¦.. So on & so forth. I have been trying to figure out a way to do this with maybe a v lookup or pivot table. However I have not been able to do so. If anyone has any ideas as to how I can do what I want to do I would be very grateful. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum based on ranges pivot table?
I would add a column (labeled "Filter") to your data. Enter a formula that
evaluates to TRUE if the date is within range. Then make "Filter" a page field in your pivot table and set it to show only TRUE. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Ranges | Excel Discussion (Misc queries) | |||
Pivot Table Data Ranges | Excel Discussion (Misc queries) | |||
Pivot table based on amount ranges. | Excel Programming | |||
Pivot Table - consolidating ranges (again) | Excel Discussion (Misc queries) | |||
using date ranges in a pivot table | Excel Programming |