Sum

[kmrowlan]

I am needing to capture the total $ of back charges grouped by department and time frame.

What formula would I use to sum the total back charges for a given month for each department?

 

[jmthompson]

Check Excel help for PivotTables. I think it should suit your need nicely.

 

[kmrowlan]

I have actually linked a table from Sharepoint to Excel and the the table cannot be modified.

I also want a formula to pull the information to automatically populate a table for KPI's we have established for each department. I perfer not to utilize a pivot table each month to gather the data if possible

Thanks

 

[Aladin Akyurek]

I am needing to capture the total $ of back charges grouped by department and time frame.

What formula would I use to sum the total back charges for a given month for each department?

Contructing a pivot table is a good option.

Other options...

On Excel 2007 or later:

=SUMIFS(SumRange,DateRange,">="&DATE(2011,4,1),
     DateRange,"<="&EOMONTH(DATE(2011,4,1),0),
       DepartmentRange,"Sales")

which totals SumRanges for the month/year pair of April 2011 and the Sales department.

For all Excel versions:

=SUMPRODUCT(
    SumRange,
    --(DateRange>=DATE(2011,4,1)),
    --(DateRange<=DATE(2011,4,1)-1),
    --(DepartmentRange="Sales"))

 

[kmrowlan]

I had the following look up but how do I include the sumrange into the formula that it is another column (G)?

=SUMPRODUCT(--('Credit DATA'!$H$2:$H$3223="Distribution - TX"),--('Credit DATA'!$L$2:$L$3223>=$A$3),--('Credit DATA'!$L$2:$L$3223<$A$4))

 

[Aladin Akyurek]

I had the following look up but how do I include the sumrange into the formula that it is another column (G)?

=SUMPRODUCT(--('Credit DATA'!$H$2:$H$3223="Distribution - TX"),--('Credit DATA'!$L$2:$L$3223>=$A$3),--('Credit DATA'!$L$2:$L$3223<$A$4))

Try...

=SUMPRODUCT(
    'Credit DATA'!$G$2:$G$3223,
    --('Credit DATA'!$H$2:$H$3223 = "Distribution - TX"),
    --('Credit DATA'!$L$2:$L$3223 >= $A$3),
    --('Credit DATA'!$L$2:$L$3223 < $A$4))

 

[kmrowlan]

Thank you that worked

 

[Aladin Akyurek]

Thank you that worked

You are welcome. Thanks for providing feedback.