# Thread: Function\VBA help (group by catagory, then by date & sum for pie charts)

1. ## 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.

2. 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. 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. ## Selects custom quarter for dates, Does not work for Null or non-date values

Originally Posted by sdckapr
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. ## 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.

7. 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...

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

swisherink (2011-12-26)

9. ## SUM with Multiple Criteria

Originally Posted by sdckapr
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

10. =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)