I've only heard their first three albums but I definitely thought Siamese Dream was overrated. I much preferred Mellon Collie and Gish. Half the album has really bland songs imo. So I definitely don't have an average opinion on the band, either. I really need to get to Adore and Machina eventually.
That's the case with all Pumpkins albums (half bland, half great) for me too, with the exception of Machina, which is amazing with the exception of 2-3 songs.
I don't really consider anything post 2005 as Pumpkins. Though I may give the upcoming albums a try since James Iha is back onboard.