+ Reply to Thread
Results 1 to 5 of 5

Sum If and Grouping Dates

  1. #1
    Forum Contributor
    Join Date
    02-10-2007
    MS-Off Ver
    Excel 2010
    Posts
    293

    Sum If and Grouping Dates

    Hello,

    Please see attached spreadsheet.

    I would like to take all the departments (column a) and all the dates (column b) and group them. Then add up the sums for columns (e and f) respectively.

    Please see desired result in spreadsheet for entire range. How do I do that?


    This is in excel 2003. So cant use sumifs...


    Thanks
    Attached Files Attached Files
    Last edited by VegasL; 10-28-2009 at 12:14 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sum If and Grouping Dates

    Either

    a) use a Pivot Table

    b) use Concatenation

    Given likely volume of data it is my opinion that you should avoid SUMPRODUCT / Array approaches.

    As an illustration of b) concept

    H2: =$A2&":"&$B2
    copied down

    E27: =SUMIF($H$2:$H$20,$A27&":"&$B27,E$2:E$20)
    copied across results matrix

    less elegant than SUMPRODUCT but more efficient

    If however you can use a Pivot Table you should do (general intro. in my sig)

  3. #3
    Forum Contributor
    Join Date
    02-10-2007
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Sum If and Grouping Dates

    Thanks. Thats a good workaround. However, why did you use &":" as opposed to $A2&$B2?



    Anyone know how to do it via sumproduct?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sum If and Grouping Dates

    Certainly but the advice was not based on ignorance of alternatives

    =SUMPRODUCT(--($A$2:$A$20=$A27),--($B$2:$B$20=$B27),E$2:E$20)

    Used in volume or with large ranges performance will be affected.


    EDIT: re: your question regards use of colon delimiter - as a general rule by adding a delimiter you reduce risk of false positives... eg:

    A1: App B1: le
    A2: Ap B1: ple

    Ax&":"&Bx -> App:le / Ap:ple -> not same
    Ax&Bx -> Apple / Apple -> same

    Simplistic example (and use of : is risky if only dealing with numeric columns for ex.) but you get the idea.
    Last edited by DonkeyOte; 10-28-2009 at 12:06 PM. Reason: added edit re: use of delimiter

  5. #5
    Forum Contributor
    Join Date
    02-10-2007
    MS-Off Ver
    Excel 2010
    Posts
    293

    Re: Sum If and Grouping Dates

    thanks bud. appreciate it sumproduct worked perfectly as well:

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1