In Mac's Finder, I have a million windows piled on top of each other when I finally find a file I want.
Is there any way to select a subfolder and when it opens, have it REPLACE the current open window in order to avoid pileup?
Did you unselect the option "Always open folders in a new window"?
Or, you could use the Columns view:
Opening one (and only one) window for each folder is called a "spatial file manager", and was common up to OS 9. In OS X, it's still used when your Finder does not show the sidebar and toolbar. Showing those is toggled by the little show/hide toolbar button in the top-right corner.
So: ensure the toolbar is shown.
Or, if you like it hidden: hold down Option while double-clicking (or while pressing Command-O or Comand-Down) to open a new folder. That will still open or activate the spatial window for the new folder (which will even have remembered its previous position) but will also close the window of the parent folder right away.
(Of course, when your Finder actually shows the toolbar, then Studer's answer may apply to your situation as well.)