Sums in a Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1434700%22%20slang%3D%22en-US%22%3ESums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434700%22%20slang%3D%22en-US%22%3E%3CP%3EI%20added%20a%20value%20to%20a%20pivot%20table%20of%20BALANCE.%20It%20automatically%20set%20the%20Summarize%20Value%20to%20COUNT.%20I%20switched%20it%20to%20SUM%2C%20but%20the%20values%20all%20come%20out%20to%20zero.%20I%20double%20checked%20my%20source%20sheet%20and%20the%20values%20are%20currency.%20Does%20anyone%20know%20why%20it%20would%20go%20to%20zero%3F%20If%20I%20AutoSum%20the%20column%20in%20the%20source%20sheet%2C%20I%20get%20the%20correct%20value.%20Any%20advice%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SEdwards_0-1591134198630.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196042iA27348347702ABF1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22SEdwards_0-1591134198630.png%22%20alt%3D%22SEdwards_0-1591134198630.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1434700%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434717%22%20slang%3D%22en-US%22%3ERe%3A%20Sums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687098%22%20target%3D%22_blank%22%3E%40SEdwards%3C%2FA%3E%26nbsp%3Bhello%20if%20you%20can%20follow%20this%20link%20and%20step%20which%20will%20help%20you%20%3CA%20href%3D%22http%3A%2F%2Fwww.officehelparticle.live%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.officehelparticle.live%3C%2FA%3E%26nbsp%3Band%20Thank%20me%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434743%22%20slang%3D%22en-US%22%3ERe%3A%20Sums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434743%22%20slang%3D%22en-US%22%3EThanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687108%22%20target%3D%22_blank%22%3E%40saradove1%3C%2FA%3E%20-%20when%20I%20follow%20that%20link%2C%20it%20takes%20me%20to%20Office365.%20I%20log%20in%20but%20it%20does%20not%20reference%20anything%20in%20regards%20to%20the%20sum%20function.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434763%22%20slang%3D%22en-US%22%3ERe%3A%20Sums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434763%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687098%22%20target%3D%22_blank%22%3E%40SEdwards%3C%2FA%3E%26nbsp%3Bsorry%20i%20was%20thinking%20it%20will%20help%20i%20couyld%20find%20other%20ways%20tho.%20if%20you%20could%20wait%20few%20hours%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434777%22%20slang%3D%22en-US%22%3ERe%3A%20Sums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687108%22%20target%3D%22_blank%22%3E%40saradove1%3C%2FA%3E%26nbsp%3B-%20sounds%20good.%20Thanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434916%22%20slang%3D%22en-US%22%3ERe%3A%20Sums%20in%20a%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687098%22%20target%3D%22_blank%22%3E%40SEdwards%3C%2FA%3E%26nbsp%3BDo%20you%20really%20check%20if%20your%20%22currency%20number%22%20is%20actual%20number%20and%20not%20text%20which%20looks%20like%20numbers%3F%20Is%20there%20any%20blank%20cell%20in%20your%20value%20column%20in%20raw%20data%3F%3C%2FP%3E%3CP%3EA%20rule%20for%20pivot%20tables%20is%3A%26nbsp%3B%3CSPAN%3EIf%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EANY%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bcell%20in%20your%20value%20column%20(field)%20contains%20text%2C%20blanks%2C%20or%20errors%20(%23N%2FA%2C%20%23REF!%2C%20%23VALUE!%2C%20%23NAME%3F)%2C%20the%20pivot%20table%20calculation%20type%20will%20default%20to%20COUNT%20for%20that%20value%20field.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I added a value to a pivot table of BALANCE. It automatically set the Summarize Value to COUNT. I switched it to SUM, but the values all come out to zero. I double checked my source sheet and the values are currency. Does anyone know why it would go to zero? If I AutoSum the column in the source sheet, I get the correct value. Any advice would be greatly appreciated. 

 

SEdwards_0-1591134198630.png

 

1 Reply
Highlighted

@SEdwards Do you really check if your "currency number" is actual number and not text which looks like numbers? Is there any blank cell in your value column in raw data?

A rule for pivot tables is: If ANY cell in your value column (field) contains text, blanks, or errors (#N/A, #REF!, #VALUE!, #NAME?), the pivot table calculation type will default to COUNT for that value field.