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

mrxwhite

New Member
Joined
Jul 1, 2004
Messages
25
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)
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

andshep

New Member
Joined
Jul 28, 2005
Messages
47
Have you tried using data>SubTotals?

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

mrxwhite

New Member
Joined
Jul 1, 2004
Messages
25
Yes - have tried that. The issue is that I need to manipulate the resultant data further before producing the charts.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,269
Messages
5,577,108
Members
412,768
Latest member
klig
Top