# re: What's the code ?

#### ashwin28

##### New Member
re: What's the code ?

Hi,

I have the following sales data which I want to analyse sales-person wise. Our company traditionally sells oranges & apples and has now diversified into selling other fruits. I would like to analyse the sales person wise sales into traditional sales (Oranges &Apples) in one column and rest of brands put together in another column.

I tried but I'm getting stumped in the code ? Pls help ?

Thanks,

Ash

SALES PERSON BRAND SALES
ALAN ORANGES 12
ALAN APPLES 22
ALAN BANANAS 37
ALAN LEMONS 17
ALAN PEACHES 7
ALAN PINEAPPLES 32
MIKE ORANGES 7
MIKE APPLES 9
MIKE BANANAS 27
MIKE LEMONS 32
MIKE PEACHES 34
MIKE PINEAPPLES 42
SMITH BANANAS 12
SMITH LEMONS 12
SMITH PEACHES 22
SMITH PINEAPPLES 22

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Ceduljko

##### Board Regular
Don't use any code - just make a PivotTable (with PivotChart, if you'd like) and Excel is going to do it for you.

#### ashwin28

##### New Member
Hi,

I used Pivot Tables but I dont know how to sum 2 columns say Oranges and Apples together and display that in the Pivot report.

Currently, the Pivot gives me sales person wise brand wise columns.

ash

#### GlennUK

##### Well-known Member
Experiment with the Grouping button.

#### Ceduljko

##### Board Regular

You're on a good way towards the solution

Another tip: Calculated fields - try to make a calculated field with some IF statements in it...

If Apples or Oranges -> Value returned: Traditional

In that way you can have additional grouping criteria.

#### ashwin28

##### New Member
Hi,

Does the grouping function work on a pivot table report ? If i try it, it says cannot do it. Also, why cannot I do a simple SUM function on the resulting pivot report by selecting the columns I want to add ?

thanks

ashwin

#### Ceduljko

##### Board Regular

Book1
ABCD
4AlanBananas37Bananas
5AlanLemons17Lemons
6AlanPeaches7Peaches
7AlanPineapples32Pineapples
10MikeBananas27Bananas
11MikeLemons32Lemons
12MikePeaches34Peaches
13MikePineapples42Pineapples
14SmithBananas12Bananas
15SmithLemons12Lemons
16SmithPeaches22Peaches
17SmithPineapples22Pineapples
Sheet1

#### GlennUK

##### Well-known Member
Grouping

Here is a screen-shot after the first phase of grouping ... after Oranges and Apples were dragged together, then selected and the Group button pressed, and then the rest of the items selected, and Group pressed again:
Book1
ABCDEFGHIJK
1SALES PERSONBRANDSALESSum of SALESBRAND2BRAND
2ALANORANGES12Group1Group2Grand Total
3ALANAPPLES22SALES PERSONAPPLESORANGESBANANASLEMONSPEACHESPINEAPPLES
4ALANBANANAS37ALAN22123717732127
5ALANLEMONS17MIKE9727323442151
6ALANPEACHES7SMITH1212222268
7ALANPINEAPPLES32Grand Total311976616396346
8MIKEORANGES7
9MIKEAPPLES9
10MIKEBANANAS27
11MIKELEMONS32
12MIKEPEACHES34
13MIKEPINEAPPLES42
14SMITHBANANAS12
15SMITHLEMONS12
16SMITHPEACHES22
17SMITHPINEAPPLES22
Sheet3

The next stage is to remove the original brand field, just leaving 2 columns of data.

#### GlennUK

##### Well-known Member
After ..

After remoing original brand field ( just drag it off the table ), it looks like this:
Book1
ABCDEFG
1SALES PERSONBRANDSALESSum of SALESBRAND2
3ALANAPPLES22ALAN3493127
4ALANBANANAS37MIKE16135151
5ALANLEMONS17SMITH6868
6ALANPEACHES7Grand Total50296346
7ALANPINEAPPLES32
8MIKEORANGES7
9MIKEAPPLES9
10MIKEBANANAS27
11MIKELEMONS32
12MIKEPEACHES34
13MIKEPINEAPPLES42
14SMITHBANANAS12
15SMITHLEMONS12
16SMITHPEACHES22
17SMITHPINEAPPLES22
Sheet3

Have overtyped the Group1 and Group2 strings with something meaningful.

Replies
10
Views
230
Replies
2
Views
494
Replies
5
Views
365
Replies
3
Views
326
Replies
10
Views
565