Complicated Excel IF statment

[Phunk]

Morning all, I am looking for some help with a Excel formula.

Currently I have a spreadsheet with various sales staff names and the profit for each deal that they have done, for example:


Deal Name Profit
1 John Smith £400
2 Joe Bloggs £270
3 John Smith £300
4 John Smith £150
5 Joe Bloggs £-70
6 John Smith £500

What I want is on a seperate spreadsheet the average profit for each of the sames staff, for example:

Name Average Profit
John Smith £337.50
Joe Bloggs £200.00

I can't quite get my head around a formula to create this, any help would be greatly appreciated.

[ATM]

I'm sure I could help - and I'm bored right now...

But I think you need to explain more. Why do you need to use IF?

[Phunk]

Ermmmm, maybe I don't!

[ATM]

I see what you are thinking. IF name matches Joe Smith then add up the whatever, right?

[marctwo]

Try sumif() divided by countif().

[Ultuous]

Sounds like a classic case for a PivotTable...

If you've not used them before, simply highlight the data, goto Data --> Pivottable and follow the wizard instructions... You'll basically want to set the layout up with SalesMan in the rows, profit in the data area... Double-clicking on the data field will allow you to set it up to show the average... Just shout if you get stuck!

[mondeoman]

A simple one that works for me is to extend the sheet to the right with more columns, one for each staff member, and then in the cell of the columns put =if(b3="John", c3,0) so that this give you a column with all of Johns profit.

Then at the top of the column put =sum(d3:dxxxx) which gives you Johns total. Repeat for each staff member

For the separate sheet you just need to call the totals cells for each one.

[fadeaway]

+1 for PivotTables

Don't be scared - sounds like you only need a simple one and the helps quite good for them.

[ATM]

I have the newer Office on this laptop [which I hate] and I cant even find the pivot table option?

[JontyR]

here you go



Up to you whether you think a pivot table is necessary....but I'm guessing this is by far the simplest!

For the forumlae I have made the look up range fixed and then select the name you are looking for. So you can copy down the formulae shown to the next line and it will work fine

Edited by JontyR on Friday 18th June 10:23

[Ultuous]

fadeaway said:

Don't be scared - sounds like you only need a simple one and the helps quite good for them.

And if that fails, have a look online for a simple tutorial... 10 minutes spent understanding what they do can really open up a world of quick and easy analysis, but they're often overlooked in favour of horrendously complicated formulae that are far less scalable and more prone to error!

[Liszt]

Pivot Table.
Use the wizard:
Select the name and profit data.
drag the Name label to rows.
drag the profit label to the data area.
Have a look at it, should give sum of profit
On the Pivot table bar select field options and sitch sum of profit to average of profit

[TonyHetherington]

JontyR said:

here you go

That's the one!

However, I too would go with a pivot table - they are cracking things once you get your head round them (which doesn't take very long) and on a simple table like that is the way to learn. You can then do a whole lot more with the same data, too.

[JontyR]

I do agree that its a good thing to use the pivot table....but sometimes the simplest way is the best way

[TonyHetherington]

[mrmr96]

My job involves a lot of manipulation of financial data on excel, just like what you're doing. Nothing is routine and things come in to me in all shapes and sizes from various exteranl sources. Everything is a one-off.

The example in the OP and an absolute slam-dunk for a pivot table. There's loads of guide online about how to use them.

I do agree, though, that if all you're needing is 'the answer' then the sumif solution presented above will do it with minimal fuss.

[Phunk]

JontyR said:

here you go



Up to you whether you think a pivot table is necessary....but I'm guessing this is by far the simplest!

For the forumlae I have made the look up range fixed and then select the name you are looking for. So you can copy down the formulae shown to the next line and it will work fine

Edited by JontyR on Friday 18th June 10:23

Cheers, thats it sorted! Thanks alot everyone!

[JontyR]

Phunk said:

JontyR said:

here you go



Up to you whether you think a pivot table is necessary....but I'm guessing this is by far the simplest!

For the forumlae I have made the look up range fixed and then select the name you are looking for. So you can copy down the formulae shown to the next line and it will work fine

Edited by JontyR on Friday 18th June 10:23

Cheers, thats it sorted! Thanks alot everyone!

You are more than welcome....if when you come to put in the real data you get into difficulties then email me the spreadsheet, Im sure I will be able to get it working for you quick enough