Forum Discussion
summarize values by sum in Pivot table not working
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
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?
- Hello, kindly verify from the source data whether the column is in fact number; not text values... You can use ISTEXT function..
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.
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?
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+H2instead of SUBSTITUTE(). When PivotTable sums correctly, please see attached.
- Hello, kindly take a sample of the pivot table report and attach please for clarity
