Nov 21 2019 11:32 PM
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?
Nov 22 2019 05:16 AM - edited Nov 22 2019 05:27 AM
Nov 22 2019 05:37 AM
@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?
Nov 22 2019 06:24 AM
Nov 22 2019 06:49 AM
pls find attached. I have taken a few lines of the raw data (as the whole file contains more than 800K lines).
Nov 22 2019 07:59 AM
Please attach your sample file, not just a picture of your results.
Nov 23 2019 07:15 AM
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.
Nov 23 2019 07:30 AM
Nov 25 2019 12:45 AM
Nov 25 2019 12:46 AM
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?
Nov 25 2019 02:14 AM - edited Nov 25 2019 02:16 AM
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")?
Nov 25 2019 02:26 AM
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.
Dec 07 2020 12:40 PM
Dec 14 2020 09:03 AM
Dec 29 2020 06:04 AM
@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".