summarize values by sum in Pivot table not working

[713326]

working in pivot table and summarize values by sum is not working (the output is "0"), whilst summarizing by count gives an output of "682185"; this as the table is having so many lines.

Any idea how to solve the issue so I have the values by sum?

[#2]

Hello, you're getting zero after Summarizing value By Sum because the field you drag and drop inside the PivotTable Values axis contains "text"... Not number

In the first caption attached, I drop Product Category field that contains text, into the Values axis

In the second caption, when I summarized values by sum, I got zeros all through

So, you need to drop field can contains summable number into the values axis and then you can summarize values by sum.


Kindly let me know if this helps

[#3]

@Abiola1 thanks for your reply, but this does not make a difference. In the raw data I ahave made sure the data in the columns are seen as numbers (number format).

Any other idea?

[#4]

Hello, kindly take a sample of the pivot table report and attach please for clarity

[#5]

@Abiola1 

pls find attached. I have taken a few lines of the raw data (as the whole file contains more than 800K lines).

[#6]

@713326 

Please attach your sample file, not just a picture of your results.

[#7]

@713326 

Even if you apply number format and values are seen as numbers, actually they could be texts. It'll be more reliable if you check any value in your source table by formula like =ISTEXT(B10) in any empty cell.

[#8]

Hello, kindly verify from the source data whether the column is in fact number; not text values... You can use ISTEXT function..

[#9]

@Riny_van_Eekelen 

Pls find attached.

[#10]

@Sergei Baklan 

sorry, but I am not really an expert in Excel.

What if the outcome is FALSE. What do i need to do? And what if outcome is TRUE? I assume I need to use ISTEXT function for those cells where i have the issue it wont sum the numbers but only count?

[#11]

@713326 

The problem is exactly what the other respondents suspected. Your formula in the "Count" column  (Col I) contains text. It uses the SUBSTITUTE function to create a text of the cells in the preceding columns.

What exactly is the result you expect to see in your column I ("Count")?

[#12]

@713326 

It's nothing to do with ISTEXT(), it only to be sure do you have text in the cell (returns TRUE) or not (returns FALSE).

As @Riny_van_Eekelen mentioned, in Count columns you have texts since SUBSTITUTE() which you use returns texts only.

Taking into account that in Excel TRUE is equivalent of 1 and FALSE of zero, you may use for Count formula

=F2+G2+H2

instead of SUBSTITUTE(). When PivotTable sums correctly, please see attached.

[#13]

@713326 

I had the "0" issue with "sum values". 

A simple refresh in the pivot column saved my day. 

 

[#14]

If you need to do these in bulk, use this guide:
https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4....

[#15]

@713326 Values inside a cell should not have space. To close the gap in the cells- Please use "Text to Column" from Data tab in excel. Sum value in pivot table will show "0" only when the value in the cell has blank space as show in my example. 

Example: for blank space in a cell. 

Do not select "Comma".