Why sequential if statements only works if the last condition is met?

There are two problems here.

First, in every if, you're overwriting the sound instead of appending to it, so you're mishandling numbers with multiple relevant factors.
Second, the else only relates to the last if, so if the number isn't divisible by 7, you'll overwrite the sound with the string representation of number. One way to solve this is to hold a boolean of whether the number has a "special" sound or not, and update it in the if statements:

def convert(number):
    special = False
    sound = ""
    if number % 3 == 0:
        sound += "Pling"
        special = True
    if number % 5 == 0:
        sound += "Plang"
        special = True
    if number % 7 == 0:
        sound += "Plong"
        special = True
    if not special:
        sound = str(number)
    return sound

EDIT:
As Hampus Larson pointed out in the comments, the special variable can be omited, and the sound can be checked directly:

def convert(number):
    sound = ""
    if number % 3 == 0:
        sound += "Pling"
    if number % 5 == 0:
        sound += "Plang"
    if number % 7 == 0:
        sound += "Plong"
    if not sound:
        sound = str(number)
    return sound

That is certainly not the way of appending strings @H2H2. You want to append to the variable sound if the input number has certain integers as a factor. This is how you can do that,

EDIT: As user Hampus Larson pointed out, the sound can be checked directly. Thanks to him!. This way we increase the performance of the code.

def convert(number):
  sound = ''
  
  if number % 3 == 0:
    sound += "Pling"
  
  if number % 5 == 0:
    sound += "Plang"

  if number % 7 == 0:
    sound += "Plong"

  if not sound:
    return str(number)

  return sound

You append strings in Python something like this, you use the + operator as you use it to add two numbers,

str1 = "H2H2"
str2 = "2H2H"

print(str1 + str2)
# H2H22H2H

str1 += str2

print(str1)
# H2H22H2H

And the way you thought you were solving the problem is wrong because the else clause only belongs to the last if clause and will only get executed if the if fails at some point.

And to answer your question Why sequential if statements only works if the last condition is met I can only say that you were doing mistakes in appending the string instead of appending them you were re-assigning the variable sound that is what makes the unexpected behaviour of your program.

Try this:

def convert(number):
    sound = ""
    if number % 3 == 0:
        sound += "Pling"
    if number % 5 == 0:
        sound += "Plang"
    if number % 7 == 0:
        sound += "Plong"
    if sound == '':
        sound = str(number)
    return sound


print(convert(35))    #PlangPlong