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!

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!