"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)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,628
Messages
5,832,762
Members
430,165
Latest member
Idjaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top