Right now, if you want to simulate 40 qubits, you need a computer with at least 16
terabytes of RAM. 41 qubits needs 32
terabytes. You can't do this in a single machine, but you could get these with enough computers linked together. But big iron doesn't scale exponentially, so at some point, you run out of atoms in the universe to use as transistors.
And 100 qubits. That needs more than 18 quintillion terabytes of RAM. In other words, it's not going to happen.
With more memory, you need more time. Every quantum operation [0] needs to touch all of that memory. And if it takes 10 ns to touch one byte of memory, then it will take hours to even do one simple operation on 36 qubits. [1] On a quantum computer, an operation would be on the order of nanoseconds.
I would say this, as a rough answer to your question: A quantum computer is practically faster and more useful when you can no longer touch the RAM necessary to simulate it in a 1 millisecond. Going off of our 10 ns rule, 1 ms = 10^6 ns. So if 10 ns allows us to do something with 1 byte, then that's 10^5 bytes. So just about 10 KB.
And, drum roll, that's just under 10 qubits [2].
[0] There are some very special cases where this isn't true, but these special cases also don't give you any sort of quantum advantage.
[1] There are some tricks you can play to speed things up, like parallelizing across processors. But now you'll have to factor in communication time, memory, and bandwidth.
[2] One factor I'm not considering is the fidelity. Superconducting qubits can be thought of as analog devices with analog noise.