Forum Discussion
summarize values by sum in Pivot table not working
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 ho...
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.
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.