Forum Discussion
summarize values by sum in Pivot table not working
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.
- 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-4477-a171-c5bad0f0a885#:~:text=Select%20the%20cells%20that%20have,converts%20the%20text%20to%20numbers.
