Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page. wins an autographed copy of Pivot Table Data Crunching from CFO and Mr Excel for his question: “Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them?
” A pivot table created from a well-formed dataset will automatically sum the numeric fields.
Hunter I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request.
What I want is: To show more than one value at the intersection besides the aggregate value.The only unfortunate thing is that I have about 10 summary pivot tables; for most I actually do want to use custom lists I've created and it's just this 1 summary view that the custom lists are screwing things up.Although they aren't quite as clever as Pivot tables have you ever thought of using SUMIFs/COUNTIFs/SUMPRODUCTs?When you have a pivot table that counts instead of sums, it is caused by one of three reasons. 1: There Are One or More Blank Cells in the Column Excel expects your numeric data to be 100% numeric.If you have a dataset with 50,000 rows of numbers and one blank cell in the middle, the pivot table will count instead of sum.