Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2009
    Location
    East Alabama
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to do some financial calculations. I have assigned purchases a category name and I want to sum the amount spent by the month it was spent and the category. My major holdup is summing by the month. Values are entered as dates. How do I get my formula to pull all of the purchases made in march?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Welcome to the lounge.

    Try this.

    Here's another one.

    Here's the BEST one! See picture below. Make sure you go to the link for instructions!
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello and Welcome to the Lounge.

    Excel's Pivot Table can work quite well for this kind of report.

    Here is a sample with all Months by Category .....

    [attachment=90429:Pivot Table all months.jpg]


    Here is a sample with March(only) by Category ....

    [attachment=90430:Pivot Table March only.jpg]

    .
    .
    You could also have columns and subtotals for items such as Vendor, Department, etc.

    The workbook is attached below. In the workbook, click on the down arrow next to Months and choose Show All to see all months.
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Aug 2009
    Location
    East Alabama
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Where does the CTRL+SHIFT+ENTER part come in?

  5. #5
    New Lounger
    Join Date
    Aug 2009
    Location
    East Alabama
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the welcomes and the helps. I have been on the site for a while but usually just search to find what I'm looking for.

    Tim, How does the data go from Dates to Months?

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello 2many - In the Pivot Table - Right click on Month>Group and Show Detail>Group and then pick Months, Quarters, Years , etc.
    The word "Month" is just a simple description change from "Date" when setting up the Pivot Table.
    A sample of your data, with all proprietary parts removed, can yield great ideas here.

    Tim

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I would use a pivot table as Tim said, but if you do want to use formulas then SUMPRODUCT is more efficient than the SUM(IF(...)) array version.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Aug 2009
    Location
    East Alabama
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The Pivot Table is definately the way to go in my case. I found the "group selection" and with this I can review my table by the month, week, biweekly, or however I want. Thanks for all of the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •