Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing mulitple line items within date ranges
Help. I have multiple data sheets that feed multiple summary views that are
in required formats and span multiple years. One series of sheets requires summing quarterly information by type of cost. Im having difficulty with using the date range in my array formula. A simplified view of my data looks like this: Jul-08 Aug-08 Sep-08 Ref Staff 152,181 152,181 152,181 1 Facilities 116,348 16,348 16,348 2 HW (Staff) 67,000 0 0 3 SW (Staff) 16,500 0 0 4 Vendor 650,000 725,000 832,543 5 HW(Ven) 289,443 88,443 0 3 SW (Ven) 36,900 0 0 4 PM Svc 36,278 36,278 36,278 6 Legal Svc 15,000 15,000 15,000 6 On a separate sheet, need to show consolidated categories of costs summed by quarter using date functions, as dates in column headings on first table most likely will change (i.e., start date of costs may change from July 08 to Nov 08) and the summed amounts in the second table need to appear in the correct quarters. Jul-Sep Oct-Dec Jan-Mar Apr-Jun 2008 2008 2009 2009 Staff Facilities Hardware Software Vendor Other Svc I have tried many approaches to this, but here is the latest one that isnt working: {=SUM((IF(A1:G1,"=07/01/2008")*IF(A1:G1,"<=09/30/2008"))*(I2:I10=3),(B2:G10),0)} Does anyone know how to make this work? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing mulitple line items within date ranges
Update to original post:
The formula shown was to calculate the Jul-Sep totals for the Hardware line. Also, the formula for the data being shown (although not correct) should be {=SUM((IF(A1:D1,"=07/01/2008")*IF(A1:D1,"<=09/30/2008"))*(F2:FI0=3),(B2:F10),0)} Thanks again for any insight. "wenb" wrote: Help. I have multiple data sheets that feed multiple summary views that are in required formats and span multiple years. One series of sheets requires summing quarterly information by type of cost. Im having difficulty with using the date range in my array formula. A simplified view of my data looks like this: Jul-08 Aug-08 Sep-08 Ref Staff 152,181 152,181 152,181 1 Facilities 116,348 16,348 16,348 2 HW (Staff) 67,000 0 0 3 SW (Staff) 16,500 0 0 4 Vendor 650,000 725,000 832,543 5 HW(Ven) 289,443 88,443 0 3 SW (Ven) 36,900 0 0 4 PM Svc 36,278 36,278 36,278 6 Legal Svc 15,000 15,000 15,000 6 On a separate sheet, need to show consolidated categories of costs summed by quarter using date functions, as dates in column headings on first table most likely will change (i.e., start date of costs may change from July 08 to Nov 08) and the summed amounts in the second table need to appear in the correct quarters. Jul-Sep Oct-Dec Jan-Mar Apr-Jun 2008 2008 2009 2009 Staff Facilities Hardware Software Vendor Other Svc I have tried many approaches to this, but here is the latest one that isnt working: {=SUM((IF(A1:G1,"=07/01/2008")*IF(A1:G1,"<=09/30/2008"))*(I2:I10=3),(B2:G10),0)} Does anyone know how to make this work? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REF# error on summing ranges | Excel Worksheet Functions | |||
Help...Summing Items Scattered in a list | Excel Discussion (Misc queries) | |||
Summing ranges | Excel Discussion (Misc queries) | |||
summing non contiguous ranges | Excel Discussion (Misc queries) | |||
Selective summing of table items? | Excel Worksheet Functions |