frequency/sort/sumproduct

[mass]

Hi there, I have 2 problems to solve:
1-
I want to sort a table of calls which includes calling destinations, type of
phone called and sum the cost of each call.
eg: here are the three columns
UK mobile 0.5c
US fixed 0.5c
UK mobile 0.4c
US mobile 0.3c
US fixed 0.1c
UK mobile 0.5c
France mobile 10c
Germany mobile 25c

I would like to return a separate table of rows for each destination and
call type with totals for the cost of the calls and sorted by most costliest:
Germany mobile 25c
France mobile 10c
UK mobile 1.4c
US fixed 0.6
US mobile 0.3

2-
some of the destinations have a special rate, but you can use only for one
destination. I would like to take the costliest destination used that has a
special rate and apply that rate as an alternative to the standard rate in a
separate column.
So, if we say that most money spent on destination is Germany but it doesn't
have a special rate, second most money spent on destination is France mobile
and it does have a special rate, then in a separate alternative price column,
use that special rate instead of standard rate. For all other destinations,
default to the standard rate.
Thanks!!

 

[Ron Coderre]

A Pivot Table could build your table automatically.

First, put column titles above your list.
I'll assume: DEST, TYPE, COST

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW:
Drag the DEST field here......Dbl-Click...Subtotals: None
Drag the TYPE field here......Dbl-Click...Subtotals: None

COLUMN: (leave this area empty)

DATA: Drag the COST field here

If it doesn't list as Sum of COST...dbl-click it and set it to Sum
Click [OK]

Select where you want the Pivot Table.Click [Finish].

That will list the Sum of COST for each combination of DEST and TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[mass]

Thank you Ron, i've just tried that and it works!
how do i now choose the most called destination?- if i use Max, it takes
totals and it takes amounts rather than the destination related to the amount.
Thanks!

A Pivot Table could build your table automatically.

First, put column titles above your list.
I'll assume: DEST, TYPE, COST

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW:
Drag the DEST field here......Dbl-Click...Subtotals: None
Drag the TYPE field here......Dbl-Click...Subtotals: None

COLUMN: (leave this area empty)

DATA: Drag the COST field here

If it doesn't list as Sum of COST...dbl-click it and set it to Sum
Click [OK]

Select where you want the Pivot Table.Click [Finish].

That will list the Sum of COST for each combination of DEST and TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi there, I have 2 problems to solve:
1-
I want to sort a table of calls which includes calling destinations, type
of
phone called and sum the cost of each call.
eg: here are the three columns
UK mobile 0.5c
US fixed 0.5c
UK mobile 0.4c
US mobile 0.3c
US fixed 0.1c
UK mobile 0.5c
France mobile 10c
Germany mobile 25c

I would like to return a separate table of rows for each destination and
call type with totals for the cost of the calls and sorted by most
costliest:
Germany mobile 25c
France mobile 10c
UK mobile 1.4c
US fixed 0.6
US mobile 0.3

2-
some of the destinations have a special rate, but you can use only for one
destination. I would like to take the costliest destination used that has
a
special rate and apply that rate as an alternative to the standard rate in
a
separate column.
So, if we say that most money spent on destination is Germany but it
doesn't
have a special rate, second most money spent on destination is France
mobile
and it does have a special rate, then in a separate alternative price
column,
use that special rate instead of standard rate. For all other
destinations,
default to the standard rate.
Thanks!!

 

[mass]

actually, i figured that out- now i just need to solve Part 2 of my question
below!!

Thank you Ron, i've just tried that and it works!
how do i now choose the most called destination?- if i use Max, it takes
totals and it takes amounts rather than the destination related to the amount.
Thanks!

A Pivot Table could build your table automatically.

First, put column titles above your list.
I'll assume: DEST, TYPE, COST

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW:
Drag the DEST field here......Dbl-Click...Subtotals: None
Drag the TYPE field here......Dbl-Click...Subtotals: None

COLUMN: (leave this area empty)

DATA: Drag the COST field here

If it doesn't list as Sum of COST...dbl-click it and set it to Sum
Click [OK]

Select where you want the Pivot Table.Click [Finish].

That will list the Sum of COST for each combination of DEST and TYPE.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Hi there, I have 2 problems to solve:
1-
I want to sort a table of calls which includes calling destinations, type
of
phone called and sum the cost of each call.
eg: here are the three columns
UK mobile 0.5c
US fixed 0.5c
UK mobile 0.4c
US mobile 0.3c
US fixed 0.1c
UK mobile 0.5c
France mobile 10c
Germany mobile 25c

I would like to return a separate table of rows for each destination and
call type with totals for the cost of the calls and sorted by most
costliest:
Germany mobile 25c
France mobile 10c
UK mobile 1.4c
US fixed 0.6
US mobile 0.3

2-
some of the destinations have a special rate, but you can use only for one
destination. I would like to take the costliest destination used that has
a
special rate and apply that rate as an alternative to the standard rate in
a
separate column.
So, if we say that most money spent on destination is Germany but it
doesn't
have a special rate, second most money spent on destination is France
mobile
and it does have a special rate, then in a separate alternative price
column,
use that special rate instead of standard rate. For all other
destinations,
default to the standard rate.
Thanks!!