The term originally referred to various pieces of sporting equipment, notably baseball bats and tennis raquets. When hitting the ball, the bat (for instance) will rebound, but there is a location along the bat where this force is completely balanced out by turning force of the bat. If you hit the ball closer to the end of the bat, the grip of the bat will try to rotate forward out of your hands, whereas if you hit it closer to the handle, the bat's tip will try to rotate forward. There is a small "sweet spot" where these two tendencies cancel out.
The term is now generally used in other fields. For instance, consider bridge-building. Long spans, notably over deep gorges, can be served only by a suspension bridge, while shorter spans can use arch bridges or cantilever solutions. In the middle is a sort of grey area, where the materials needed to construct an arch (for instance) would be about equal to the expense of the cabling needed for a suspension design. This is the "sweet spot" for the cable-stayed bridge, which reduces the cabling and the materials.