Complicated excel formula help wanted!

Trow

Well, I think it is complicated anyway!

My boss wants to cut down time and effort (fair enough!) by combining two spreadsheets into one.

Basically, I will have a list of names (the names will be repeated), and a list of amounts.

Is there a way of getting a total of the amounts relating to individual names?

An example - say we had a spreadsheet detailing employee expenses as follows:

Column A B
George £30.00
Alison £25.00
Mary £12.50
Peter £15.75
Sean £72.65
Alison £101.65
Mary £42.45
Sean £31.20
Peter £80.20
Peter £10.50
George £21.00

Could you then have total at the bottom for each individual employee?

Thanks!

wigginsmum

You'd have to have the total column on the right so that it's on the end of the line for each employee. So if they had amounts in Columns B and C, you'd make column D the total column and use the SUM function to get the total. If you're pulling an amounts from a different spreadsheet, it's possible to specify worksheet/cell for the total figure - so cell B3 on Sheet2 would be Sheet2!B3 (I think).

tru

The easiest way is to have a column for each person with their amounts under their names, then use autosum.

chrisw2k

Does it have to be in Excel? Sounds like you might be better using a database product such as MS Access... troos suggestion is probably the simplest however provided you dont have 100's of employees!

tru

chrisw2k wrote:

... troos suggestion is probably the simplest however provided you dont have 100's of employees!

:doh: I didn't think of that

How about each person has a row with the name in column B, then amounts in c,d,e etc. Column A would give you the total.

tru

Trow, I've just made one using the names and £s you used in your post. PM me if you want it

Trow

The spreadsheet is much more complicated than that - it really would need to be some kind of formula, rather than changing the layout.

I did cursorily consider access, but the reporting requirements could be complicated, and I don't have much access knowledge.

Thanks for the input though!

Altarf

The formula 'SUMIF' will do exactly what you want.

tru

Assuming you're starting in row 1 with George, his amounts would be in B1, B11. Click the cell you want the total in then type '=', click cell B1, then the '+' button on the keyboard, click cell B11 and press enter. That'll give you the total. When you add more expenses for George, go to his 'total' cell and add to the formula '+' and click the next expense cell and press enter.

tru

Altarf wrote:

The formula 'SUMIF' will do exactly what you want.

I knew there would be an easier way than mine

spotter

I would use a PivotTable (it's on the Data menu). No extra formulae or columns required.

hth