There's two layers to its utility. For overall circuit design (not IC design specifically) it's a tool for simulating the analog behaviour of a circuit. Two frequently used modes are "transient response" and "AC steady state response". The first shows you the voltages and currents in the circuit in response to a changing input (e.g. the input goes from 0V to 3.3V). The second sweeps across a user-specified range of frequencies and shows you the voltages and currents to expect across that range. Linear devices (resistors, capacitors, inductors) are generally easy to work out by hand, but when you start adding non-linear devices (diodes, transistors, etc) it gets pretty impossible pretty quickly to hand calculate with any real degree of accuracy. Many vendors provide SPICE models for their parts, so you can often get decently accurate simulation outputs. To your question "when is it not useful?", you have to be very careful to look at the simulation output and use your brain to determine whether or not the results actually make sense; it's quite possible to either mess up your netlist or device models and get output that is completely wrong but still simulates fine.

For IC design specifically, all of the above is still true but with a little extra. One of the challenging parts with IC design is the fact that every fab/process behaves differently and at smaller and smaller feature sizes those behaviours differ more and more from the basic first-order models. To make this kind of design even possible, the fabs do their best to characterize and model what transistors made on the process will do as a function of their geometry. This is often considered to be highly proprietary data and most of the commercial simulators provide a mechanism for the fab to release these model files (sometimes called PDK: process development kit) in an encrypted format that the simulator can decrypt but the user can't see. The designer can parameterize the transistors in their design by manufacturing parameters (e.g. gate width, well depth, etc) and get reasonably accurate simulations... with the same caveat as above: you always have to be vigilant to ensure that the output values actually make sense; the simulator can produce absolutely nonsensical results if the input is bad.

The other "not useful" part is that the simulation runtime can get very long as the complexity of the design grows. I haven't been in that industry for almost 20 years now but back then you were generally limited to simulating small subcircuits (say 100s of components) if you wanted anything close to quick results. Simulating larger designs would take days or weeks.

As another poster has said, SPICE is used for simulating the analog blocks of an integrated circuit and it is also used by those who design the components of a library used by the digital designers, which contains blocks like logical gates and flip-flops, to simulate such blocks, e.g. for estimating the time delays through gates or the maximum clock frequencies for flip-flops.

When an IC includes analog blocks, not only digital (but most digital ICs also include a few analog blocks, e.g. voltage regulators or oscillators for clock generation), the design of the analog blocks passes through a few stages.

First the schematics of the analog blocks is designed. Then it is simulated with SPICE or similar programs, in order to verify if it satisfies the design criteria and to optimize the values of the component parameters. The simulation results cause the redesign of the schematics, until all seems fine. Then the geometric layout of the analog blocks is designed.

After that a dedicated program analyzes the layout and it computes the value of parasitic components, e.g. parasitic resistances, capacitances, self inductances and mutual inductances. All the parasitic components are back-annotated on the initial schematics. Now the analog blocks are simulated again with SPICE and this time the simulations take a longer time as there are a lot of extra components. If the results are not acceptable, the schematics is redesigned and all the cycle is repeated.

The simulations with SPICE usually include Monte Carlo simulations that verify that the designed blocks satisfy the performance criteria even when the values of all parameters are varied randomly, like they will vary as a consequence of the production processes. The environmental conditions, e.g. temperature and supply voltages can also be varied randomly in such simulations. Therefore the total number of simulations of a circuit, resulting from the need to search optimum parameters and the need to search the worst cases during Monte Carlo simulations, can be very large, thus the simulation times, even on server farms, can reach weeks. Modern SPICE programs usually run only the GUI on the workstation of the designer, while the actual simulations are distributed on multiple servers.

When the simulation with the schematics back-annotated from layout succeeds, and the concurrently designed digital blocks of the IC are also finished, then the whole integrated circuit must be simulated. Because this is very time-consuming, usually only simple things are simulated, like the booting sequence of a CPU.

For the simulation of a whole IC, one typically uses a mixed-mode simulator, which uses a Verilog/VHDL simulator for the digital blocks, coupled with a SPICE simulator for the analog blocks. To couple the blocks, special digital-to-analog and analog-to-digital components are incorporated in the schematics of both the analog and digital blocks.