It would only work if the elephants were completely disciplined and ignored injury, death of friends, and noise. But they could take down all but thick walls, and they can absorb a lot of bullets / almost unlimited arrows. They wouldn't have battlefield medicine (no hands) and their population does not grow very fast.
With all the excellent counterpoints to my comment by you and others in mind,
> It would only work if the elephants were completely disciplined and ignored injury, death of friends, and noise
How about drugged elephants then? What if, in this increasingly sci-fi alt history setting, the elephants were just smart enough to figure out a) what kind of plant, mushroom or whatever makes them go berserk, b) realize berserk elephants are much better at effectively fighting humans, c) were willing to make individuals sacrifice themselves for the herd, and d) were able to consistently use berserk assaults as a battle tactic?
I know it's a lot of assumptions, but I won't have mere historical facts make me let go of the image of OG ultralisks attacking pre-industrial humanity.
> realize berserk elephants are much better at effectively fighting humans
Here's the fly in the ointment.
Berserk elephants would not be better at fighting in the same way that berserk humans aren't better at fighting. It's discipline and order that make an effective fighting force.
If you know anything about ancient hand-to-hand battles, you'd know that there were very few casualties during the battle proper. It's only when one side's formation broke and their soldiers became chaotic and disorganized that the real casualties occurred.
If I were to guess, I'd guess that you're getting this idea from movies where the main character harnesses emotion to become more capable and powerful. In the real world, emotional people die quickly and are generally ineffective.
Tie that up with some mis-reading of ancient Mayan calendar that turned out to be talking about 2024, a comet crashing into the Moon, with elephants reacting to the scarred lunar surface by synchronous jumping. Cue emergency space mission, where our heroes race against the clock to re-paint the Moon, so the elephants stop jumping while there's still some civilization left to return to.
Pre-gunpowder humanity would be toast.