On one tab we have a list of names with the amount they are allocated to a particular project. One name may appear several times if they are working on many projects. Many people can work on the same project but that doesn’t matter for this bit. See below

Alan 100 Admin
Barry 50 Project 1
Barry 25 Project 2
Clare 20 Admin
Clare 20 Project 1
Clare 20 Project 2

On another tab we have a list of the people and I want to know their total allocation. See below

Alan 100
Barry 75
Clare 60
Darren 0

I can use vlookup but that only finds me the first value and sumif doesn’t seem suitable either.

Any suggestions?

Paste the formula if you can.

Thanks

Pivot table?
Sumif? =(columnA,name,columnB)

SUMIFS? but as above this is classic pivot table stuff.

Sumifs

emailed you solution, pivot works as does sumif

Power bi 😉 pivot tables on steroids and pie charts to die for (and free)

Thanks all!

Pie charts should die. Not to die for. Useless way of displaying data.

Pie charts should die. Not to die for. Useless way of displaying data.

I missed the ironic part but why so bad, they are spot on for lots of stuff (anything that is % for a start)

Match and Index? And then Pivot the results?

Pie charts

they are spot on for lots of stuff

They’re really not.

Stephen Few’s thoughts

sumproduct will do what you want. I use it all the time for the the very same thing

See here too on why pie charts are utterly useless.

They’re just about acceptable when there are only 2 pieces of data, but when there’s any more you can’t judge the relative size of each, so people add data labels, so you just read those. Which is retarded.

sumproduct will do what you want. I use it all the time for the the very same thing

Unnecessary overhead vs sumifs when used over two like vectors.

Although Sumproduct is a great function 🙂

so people add data labels, so you just read those. Which is retarded.

See also: gauges. Horrendous waste of space/colour

*high fives Rob Hilton*

Sumproduct might be overkill for your exact need here but it’s very versatile and I’ve found it hugely useful for applications like this. So it’s a great one to learn.

njee20 – Member – Quote
*high fives Rob Hilton*

“But that’s what they want” said many managers to me.

No it isn’t – they don’t know what they want until I give them some ideas about what’s possible… Apparently I come across as aggrogant – it’s not true; I just know my subject well

Thanks for the input. SUMIF worked as per the lovely detailed email I received* and so I have now sacked Darren for not doing any useful work 🙂

*This is why I love STW over Excel help

Over on singletrackroad.com Darren is now starting a rant about cyclists not stopping at red lights because they’re too busy answering excel questions that have got him the sack. 😉

Now answered, this thread needs more Pie chart referencing.