Excel Help

Baldie

Hi,

I have a spreadsheet which has salespeople in column A, and customer balances in column C. Basically, I need to run a formula at the end of the sheet to total up the customer balances in relation to A, B, C etc...

I was thinking of using a V look up, but how would I total the values?

A - Customer Z - 1,000
B - Customer Y - 2,000
C - Customer X - 3,000
A - Customer W - 4,000
B - Customer V - 5,000
C - Customer U - 6,000
A - Customer T - 7,000
B - Customer S - 8,000
C - Customer R - 9,000

osullic

Could you not just use the auto sum function or am I oversimplifying the problem?

Baldie

No, I need the totals of A, B, C seperatly. The Spreadsheet has 200+ lines, and re-sorting throws out some other formulas.

The Big Red Button

Do you know how to do a Pivot table?

EL_Loco

sort by column A,
use subtotals for each change in column A, SUM column C

it will give you a subtotal by sales person.

dilallio

Attachment not found.

This is another method of doing it.
Add 3 cols, one for Salesperson A, B, C and use a simple formula like the one in the attached worksheet to calculate the sales associated with each. They can be then totalled at the bottom.

The Big Red Button

EL_Loco wrote: »

sort by column A,
use subtotals for each change in column A, SUM column C

it will give you a subtotal by sales person.

He doesn't want to re-sort the table though!

OP if you want to do a pivot table (I'm hoping you're using the same version of Excel as me!) :

• Ensure you have a title for each column (e.g. Salesperson, Customer, Total)
• Go to "Insert" "Pivot Table" "Pivot Table."
• Select the table range (doesn't matter if you include extra columns.)
• Select where you want to place it (probably existing worksheet), and click a cell
• Rightclick on the box that appears, select "Pivot table options", go to "Display", and make sure the box for Classic layout is ticked.
• From the Field List to the right of the screen, drag and drop the column A heading (e.g. salesperson) to the table, to where it says "Drop row fields here"
• Then, drag and drop the column C heading (e.g. total) to the table, to where it says "Drop data items here"
• Rightclick on any of the results showing in the "data items" you just added. Go to "Summarise data by" and ensure "sum" is selected.

Results will show up as attached (is this what you're looking for?)

If any changes are made to the balances, you will need to right click and refresh the pivot table.

It's really not as complicated as it looks above! Once you've done it a couple of times, it's a really easy way of sorting and manipulating data.

TommyTippee

Use "SUMIF" if you don't need the individual customers listed.

Jagera

Like chatterpillar said. Pivot table. Forget the rest IMO.

TommyTippee

bw wrote: »

Like chatterpillar said. Pivot table. Forget the rest IMO.

rubbish

Jagera

TommyTippee wrote: »

rubbish

excellent point well made. but do feel free to expand on your answer. In anticipation of you asking the same, here's my points

- the file has 200+ lines
- I assume its not just A, B, C that he's wanting a SUM for
- this calculation will be done potentially on a regular basis
- using a pivot table will give even MORE ability to analyse the data in other ways

If I am wrong on all of the above, that its only A, B, C & its a once-off, then yeah a SUMIF would be OK..

Taltos

The nice thing about the pivot table is that if you have a value in the column you were not expecting it will catch it for you.

They do take a bit of playing around - and for someone new to them they can appear offputting, but they are quite good.

If you need tips/tricks let us know - but please confirm the version of excel as they have changed a little - on 2007 I still update it back to the old display - but that is just me.

TommyTippee

bw wrote: »

excellent point well made. but do feel free to expand on your answer. In anticipation of you asking the same, here's my points

- the file has 200+ lines
- I assume its not just A, B, C that he's wanting a SUM for
- this calculation will be done potentially on a regular basis
- using a pivot table will give even MORE ability to analyse the data in other ways

If I am wrong on all of the above, that its only A, B, C & its a once-off, then yeah a SUMIF would be OK..

crapóla

*sound of flushing toilet*

Ste1605

I have another Excel problem - I need to create a bar chart with two rows of values. I need the Ticket numbers on the X axis and Duration on the Y. Any help. I have the new version of Excel. Any help greatly appreciated.

ajc100

Ste1605 wrote: »

I have another Excel problem - I need to create a bar chart with two rows of values. I need the Ticket numbers on the X axis and Duration on the Y. Any help. I have the new version of Excel. Any help greatly appreciated.

Assuming data is in column A and B:
Click on empty cell.
On 'Insert' Tab click Column, choose which chart you want.
On 'Design' Tab click Select Data.
Click Add.
Series name: =Sheet1!$A$1
Series value: =Sheet1!$B$2:$B$18
Click OK.
Under 'Horitontal(Category) Axis Labels' click Edit.
Axis label range: =Sheet1!$A$2:$A$18
Click OK twice.

Taltos

Ste1605 wrote: »

I have another Excel problem - I need to create a bar chart with two rows of values. I need the Ticket numbers on the X axis and Duration on the Y. Any help. I have the new version of Excel. Any help greatly appreciated.

Assuming you selected your data and then chose Insert Bar Chart (and not column)
1. Select chart
2. Right click & select Select Data (also avail on the menu)
3. Select Ticket under Legend Entries
4. Select Remove
5. Now - under Horizontal Axis - select Edit
6. Select range as your data range from ticket - this puts it on the vertical axis as the chart is a bar not column type

If you want Ticket on horizontal - X axis - then at my initial assumption choose Column Chart & repeat steps 1 to 6 above.

I miss the old excel - this is just not intuitive.

Ste1605

Thanks very helpful.

Ste1605

Hi. I need help with Excel and some vlookup formula.
I need a formula that if the value in column D on Sheet2 is either FTID FTS or Priced at cost to look up a value based on on the data in column A from sheet "All Prices". In this sheet I also only want to pull mid prices from data provider that isnt FTID FTS.
I have attached the sheets.
Any help is greatly appreciated.

Taltos

Ste1605 - am out of the office on PTO for a few days - if you still need this let me know.
Basically though I expect the solution to be a nested IF statement with the Vlookup - cannot check now as this PC does not have office.

eg.
=if(vlookup(statement1)=value,vlookup(statement1),vlookup(statement2))

Will check back on Monday when I have excel again.

Taltos

Hi Op - a few issues with what you are looking for.

All Prices
Remarks|Asset Id|Price Currency|Price|Price Point|Data Provider|Price Type|
|008725W|GBP|95.125|FT-CB-LT|FTID FTS|MID|


vs

Sheet 2
FM Sec|ISIN|Winning Provider|Winning Price|FTID Price|(gap)|Difference between FTID and|
008725W|XS0184565249|S P|94.42|95| |-0.006142766|



In order to use vlookup or pull data between sheets you need something to uniquely identify them.
If that is meant to be your Winning Provider column (C) then you need to consolidate [All Prices] otherwise vlookup will just return the first value.
This still holds true if FM Sec is Asset ID - you need a consolidatd list instead of the current sheet1 - one option is run it thru a pivot or use something else as your key.

A simple formula you can use on [Sheet2] to highlight the rows that meet your criteria is
=IF(C6="FTID FTS",1,IF(C6="Priced at Cost",1,0))
Then just filter by 1 to see the matching rows.