Is there a megapixel "sweet spot" for each sensor size?

We know it's not all about megapixels, but the size of the pixels as well. We know about the noise and thus, dynamic range tradeoffs (and we know that somehow manufacturers have continued to push back the limits that the pixel-race naysayers used to warn us about 15 years ago.

We have 50 megapixels in 24,36, but the image doesn't look quite as nice as 50 megapixels in medium format.

But is there a "sweet spot" of the number of pixels for a sensor size? At least this year? At what point is it not worth counting pixels for a cell phone and time to go to 4/3? At what point is it time to stop counting pixels in 4/3 and go to APS? To 24x36? To medium format?

Any way to determine that?

Yes,

But it depends on what you're photographing. For terrestrial photography, it's hard to meet this threshold in a meaningful way. But in something like astrophotography, using pixel size relative to aperture to help relate sampling or resolution to the intended capture of detail is pretty important because there is a point where it's not enough and is undersampled, or too much, and oversampled.

For real world application of terrestrial photography, you'll note that large pixels tend to look "sharper" than lots of small pixels. So ultimately it depends on what you're trying to do with the data.

Is there a sweet spot? Sure, but only relative to both the recording and presentation of the data in a context.

Very best,

Probably the factors are too convoluted to jump to conclusions. We know that the 6D at 153 pixel/mm was better in noise than the 5DIII at 160 pixels/mm. We now have the 5DIV at 187 pixels/mm as the current banner carrier for Canon, yet Sony has even higher pixel/mm with comparable (if not better than 5DIV) noise. And that is within a single format size.

There are some "sweet spots" and they are based upon 1) the sensor size and 2) the target client.

For many years, (over 8), the 12 mp sensors, both cropped and full frame were the staple of editorial and sports photographers. This had to do with the maximum size these image files were reproduced.

Now it seems that editorial and sports photographers have new tools and they include sensors with 21 mp, but more importantly superior autofocusing schemes

Many landscape and architecture photographers like the full frame cameras which have from 36 to 40 mp sensors It is not unusual to make 4 x 6 foot prints from the image files produced using these sensors. Editorial and sports images are usually one full magazine page or less, more like a 1/4 page.

Only using the cameras under actual "real world" conditions will give you the most accurate answer.

Perhaps it is for that reason, I own an old 12 mp Nikon D3s, a Nikon D500 and Nikon D800e. No matter what assignments I get, one of those cameras is the "best" choice.

Time makes a difference, as the technology to squeeze pixels into a certain area has evolved over the years.
Another thing you didn't really mention is the simple fact that more light reaches a larger sensor. That will always be true, regardless of tech level. If you get the same amount of light per area, then a twice as large sensor will have twice as much light representing the subject. That improves signal to noise ratio, no matter what else you do.