"Group by" in Excel - summary data from detail row

[mrxwhite]

Hi - I have a set of spreadsheets that I want to produce some charts from. However, the rows need to be summarised first as I have repeating groups (see data below):

• Worktype CCCode Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
  Worktype1 CCCode1 2 2 2 2 2 2 2 2 2 2 2 2
  Worktype1 CCCode1 70 70 70 70 70 70 70 70 70 70 70 70
  Worktype1 CCCode2 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8
  Worktype1 CCCode2 5.3 5.3 5.3 5.3 5.3 5.3 5.3 5.3 5.3 5.3 5.3 5.3
  Worktype1 CCCode2 12.3 12.3 12.3 12.3 12.3 12.3 12.3 12.3 12.3 12.3 12.3 12.3
  Worktype1 CCCode3 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8
  Worktype2 CCCode1 1 1 1 1 1 1 1 1 1 1 1 1
  Worktype2 CCCode2 3 3 3 3 3 3 3 3 3 3 3 3
  Worktype2 CCCode2 2 2 2 2 2 2 2 2 2 2 2 2
  Worktype2 CCCode3 1 1 1 1 1 1 1 1 1 1 1 1
  Worktype2 CCCode3 60 60 40 40 40 40 40 40 40 40 40 40
  Worktype2 CCCode4 5 5 5 5 5 5 5 5 5 5 5 5
  Worktype2 CCCode4 27.5 27.5 27.5 27.5 27.5 27.5 27.5 27.5 27.5 27.5 27.5 27.5
  Worktype2 CCCode4 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8 8.8

I want to see one row for each combination of work type and CC code (like a SQL group by). So, in the above example, for Worktype1, CCCode1 there should be 12 values of 72 (sum of the first two data rows) and so on.


Thanks

Mark

(apologies for formatting - can't use HTMLMaker from work)

 

[andshep]

Have you tried using data>SubTotals?

with the settings at each change in CCCode use function Sum ?

 

[mrxwhite]

Yes - have tried that. The issue is that I need to manipulate the resultant data further before producing the charts.