How does it explain the SUM function working in the four other worksheets with the same formulas and formats (just different data) ?
Yes, different data.
As outlined where SUM returns 0 the values being summed are not numbers per se - they are in fact Text Strings (numbers stored as text)
To illustrate by means of example...
Create a new sheet
Format A1:A2 to be Text (leave all other cells)
Enter 10 and 20 into A1 and A2 respectively
Enter 10 and 20 into B1 and B2 respectively
In A3 enter: =SUM(A1:A2) and copy across to B3
The result in A3 will be 0 and B3 will be 30
This is because A1:A2 are text strings (numbers stored as text) whereas B1:B2 are actual numbers.
In A4 enter: =A1+A2
Copy to B4
The result in both A4 & B4 will be 30.
The act of addition explicitly coerces the values to numbers, ie the text strings in A1 & A2 change from "10" and "20" to 10 and 20.
Now change A1 and B1 to Apple
You will now find row 4 is generating #VALUE! errors - this is because Apple can not be coerced to a number.
You will note that B3 however now returns 20 - this is because SUM does not try to coerce the text strings it simply ignores them.
Does that clarify things ?