Question about Summing in Excel

[JVZ]

I have a question about summing based on criteria. I have a excel sheet with data showing totals by day. I want to create a summary column that shows to totals by month, i.e.:

Month Sum of Completed calls
Dec 514
Jan 8625

I'm currently doing this w/PIVOT tables, just wondering if this can be done using a formula instead.


/*
* Data by day
*/

Date CIC Sum of Completed calls
12/25/2002 W483 3
12/26/2002 W483 64
12/27/2002 W483 106
12/28/2002 W483 65
12/29/2002 W483 26
12/30/2002 W483 158
12/31/2002 W483 92
1/1/2003 E953 3
1/1/2003 W483 74
1/2/2003 E953 6080

Thanks in advance for your time!

 

[bryanbayfield]

Just bring your annoying paperclip up and ask for info on teh SUMIF function - should give you all you need.

 

[DaleWatson123321]

JVZ,

An excellent alternative to Pivot Tables - especially when a more "dynamic" application is desired, is the use of Excel's database formulas, and (selective) data extraction.

Using this option, it's possible to set up summary reports that provide a matrix of totals - by whatever criteria is required - e.g. Totals By Month, or Totals By CIC By Month.

Such applications can be set up so that all one has to do to generate separate reports (e.g. each CIC per Month Per Year), is to simply pick the CIC from a pulldown list.

If the database will contain data for more than 1 year, generation of reports for different years is only a matter of changing ONE cell (for the year). Or, if the application will only be used for ONE year's data, changing over to the new year only involves changing the ONE cell.

If desired, VBA code can easily extract the "data behind any totals". The data is extracted to a separate sheet which is set with titles, formatting and print settings.

If you're interested in this option, email me, and I can provide you with assistance.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JVZ]

First off thank you for taking the time to response to this topic.

bryanbayfield - I took your suggestion and did some research, and found out for what I'm doing I should be using array formula (the famous CRTL + Shift + Enter). So here is what I came up with:

**** BY MONTH Summary ****
=SUM(($A$2:$A$120<=E6)*($A$2:$A$120>E5)*($C$2:$C$120))

**** BY MONTH BY CIC Summary ****

=SUM(($A$2:$A$120<=E6)*($A$2:$A$120>E5)*($B$2:$B$120=$F4)*($C$2:$C$120))

**where E6 = month, E5 = last month and F4 = CIC

Dale - Once I get all the loosely coupled requirement straight, you will probably see an e-mail from - (hope you don't mind).

Thanks again for your help!

Thanks

AK
- Making progress everyday