11-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?
11-22-2019 05:16 AM - edited 11-22-2019 05:27 AM
11-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?
11-22-2019 06:24 AM
11-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).
11-22-2019 07:59 AM
Please attach your sample file, not just a picture of your results.
11-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.
11-23-2019 07:30 AM
11-25-2019 12:45 AM
11-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?
11-25-2019 02:14 AM - edited 11-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")?
11-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.
12-07-2020 12:40 PM
12-14-2020 09:03 AM
12-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".