I’ve got a list of projects, with customer name and value.
I want to produce a list which has the total value per customer which I can then sort to find who spends most (who doesn’t?).

Is there a straightforward way to do this via formula, something along the lines of a sumif, but without having to create a list which only contains each customer name once?

If not, how can I consolidate the original list of customer names (with many duplicates) to contain each customer name once?

Ta!

Sounds like a pivot table to me…

you tried making a pivot table from your data sheet?

Never done that before – I’ll have a play!

Making a list of unique names is really is with the Remove Duplicates function.

SUMIF will work,

=SUMIF(B2:B12,”Customer Name”,C2:C12)

Where B2:B12 contains your customer names, the quotes contains the name you’re looking for, and C2:C12 contains the value of the projects. It doesn’t matter how many times each customer appears in the list.

Pivot table will do it, just make sure that you have consistent spellings or a unique code for each customer.

Pivot table far easier assuming you’ve always spelt them the same way.

Took a bit of fiddling but worked a treat with a pivot table! Impressed the rest of the office too 😀

Could have done it with sumif, but would have to create a seperate client list first removing all the duplicates as we have a lot of repeat clients, so pivot saved a lot of effort!

Cheers all

god wait till you see flash fill….. 🙂

so pivot saved a lot of effort!

Getting a unique list is easier than you might think. Sort A>Z. In the adjacent col e.g. B2 enter =if(A2=A1,”0″,”1″). Double click to extend the formula. Copy and paste B col as values, all the 1s will be unique so just sort by that col.