Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    MS Excel 2007 (group by catagory, then by date & sum for pie charts)

    I have attached a sample worksheet to better explain what I am trying to do; I know some of this is probably easy but I can't seem to get the results I need with any of my attempts thus far. I have a worksheet that lists several entries (projects), each project belongs to a team (group). As the worksheet is updated by the team a (projected date) and (amount) are entered into the sheet. Some of the projects are unknown dates and unknown amounts so the capability to contain TBD or Null values will need to be allowed.

    The tracking quarters for the year are not the same as a standard calendar, but rather:
    Q1 = 11/01/2011-01/31/2011
    Q2 = 02/01/2012-04/30/2012
    Q3 = 05/01/2012-07/31/2012
    Q4 = 08/01/2012-10/31/2012

    I am attempting to do the following but having some difficulty in excel.
    1. Have the Quarters determined based on the date entered in the date cell (Column C) and auto-fill in Column D for use later.
    2. Have the Group auto-fill based on the project entered using a list located on a separate sheet (GroupList).
    3. Using (Group) and (Quarters) sum the total (amount) for each quarter by group.
    4. Using the Sum totals, create a Pie Graph for Each group that represents a total allocation amount and shows the projected amounts by quarter.

    Thank you for your help.
    Attached Files Attached Files
    Last edited by swisherink; 2011-12-20 at 11:39. Reason: Change posted title

  2. #2
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I was able to find a function online for determining a Quarter from a Date using =ROUNDUP(MONTH(C2)/3,0) , but this is not providing the quarters that I need.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Try:

    =ROUNDUP((MONTH(c2)+2)/3-4*(MONTH(c2)>10),0)

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    swisherink (2011-12-20)

  5. #4
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Selects custom quarter for dates, Does not work for Null or non-date values

    Quote Originally Posted by sdckapr View Post
    Try:

    =ROUNDUP((MONTH(c2)+2)/3-4*(MONTH(c2)>10),0)

    Steve
    Steve, Thank you for the quick help on this, the function you provided works perfectly for calculating the custom quarters I need. Is there any way to make this work for Blank or Non-Date Values? When using this function a blank value in C2 results in a Q1 result when it is not projected for Q1. If I enter TBD (To Be Determined) it results in a VALUE# error.

  6. #5
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    IF Statement with Multiple factors

    After moving the columns in Projects! over a couple so they didn't line up with the columns on GroupList!, I was also able to use =VLOOKUP(C2,GroupList!A2:B10,2,FALSE) to get the Groups to auto-fill in Column D (Group) based on the entry in Column C (Project). I attempted to copy this function down to the below rows but it auto adjusted the range in GroupList! rather than maintaining the complete list. Is there a way to apply this lookup to the entire column so new entries added get the same function?

    I was also able to get the Pie-Graph to appear using manually entered data located in K3:N3. See Attached for update.

    Still need some type of IF statement for the data to pull from the left table (C2:G11) over to the right table (K3:N6) based off of the following:

    Cell K3 = The SUM of the (amount) of only AS (projects) projected in Q1 (quarters)
    Cell L3 = The SUM of the (amount) of only AS (projects) projected in Q2 (quarters)
    ...
    Cell K6 = The SUM of the (amount) of only BC (projects) projected in Q1 (quarters)
    ...

    Thank you.
    Attached Files Attached Files
    Last edited by swisherink; 2011-12-20 at 14:45. Reason: Update status and current issues

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Q1. In F2 (copy down the column):
    =IF(ISNUMBER(E2),ROUNDUP((MONTH(E2)+2)/3-4*(MONTH(E2)>10),0),"")

    This will put a null string if the date cell is not a number...

    Q2, if I understand correctl,in K3 (copy down the column and accross the rows):
    =SUMPRODUCT(($D$2:$D$11=$I3)*($F$2:$F$11=VALUE(RIG HT(K$1,1))),$G$2:$G$11)

    This will sum the values in column G when the groups in column d match value in Column I and the Quarter in Col F match the last right digit of row 1)

    Steve
    PS I did not get the example numbers you list. The numbers you get woul be obtained if the Q2 "orange" project was grouped with "AS" rather than "OC". I presumed the example values were mistaken...
    Last edited by sdckapr; 2011-12-21 at 05:07.

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    swisherink (2011-12-26)

  9. #7
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    SUM with Multiple Criteria

    Quote Originally Posted by sdckapr View Post
    Q1. In F2 (copy down the column):
    =IF(ISNUMBER(E2),ROUNDUP((MONTH(E2)+2)/3-4*(MONTH(E2)>10),0),"")

    This will put a null string if the date cell is not a number...

    Q2, if I understand correctl,in K3 (copy down the column and accross the rows):
    =SUMPRODUCT(($D$2:$D$11=$I3)*($F$2:$F$11=VALUE(RIG HT(K$1,1))),$G$2:$G$11)

    This will sum the values in column G when the groups in column d match value in Column I and the Quarter in Col F match the last right digit of row 1)

    Steve
    PS I did not get the example numbers you list. The numbers you get woul be obtained if the Q2 "orange" project was grouped with "AS" rather than "OC". I presumed the example values were mistaken...
    Steve,

    Thank you for the help correcting the Null string for part Q1 of my project. The Q2 calculation also worked out perfectly for getting the SUMs I needed. I attached an updated copy of the sample sheet, yes the example numbers where incorrect, sorry about that.

    Also, when I attempted to copy down =VLOOKUP(C2,GroupList!A2:B10,2,FALSE) from D2 to D3 it automatically adjusts the to =VLOOKUP(C3,GroupList!A3:B11,2,FALSE) which eventually puts the lookup values outside of the list on the second tab. Is there a way to modify the VLookup string so that when copied down it holds the list as GroupList!A2:B10?

    -L
    Attached Files Attached Files
    Last edited by swisherink; 2011-12-26 at 14:54.

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =VLOOKUP(C2,GroupList!$A$2:$B$10,2,FALSE)

    The Dollar signs($) lock the range when copying. A dollar sign before the column, makes the column an absolute reference and a dollar sign before a row makes the row absolute. With no dollar sign the reference is relative.

    Steve

  11. The Following User Says Thank You to sdckapr For This Useful Post:

    swisherink (2011-12-28)

Tags for this Thread

Posting Permissions

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