I can use Pivot tables to do what i need apart from one thing.

When adding fields into the ‘Values’ section, it defaults to count the number of values rather than sum. I can change each manually, but on a table with multiple columhns, it’s a PITA. how can i change default to be sum?

That normally means there’s some non number values in those columns. If the content is sorted it will default to sum rather than count.

From what I understand you cant change the default, but you can use an add-in to do it for you

http://www.contextures.com/xlPivotAddIn.html#!

monkfish wins a prize! Some zeroes weren’t zero, but were blanks. Find and replace those and it seems to be fixed!