As other's have said, "it depends". Monetarily, the membership can be worth it after buying a few hundred $ worth of certain things.* That can be in a single trip or multiple trips. It's certainly possible to go to CostCo to stuck up several months worth groceries at once and make a membership worth it, even adding in the cost of driving.
It can be a trap if you shop "in the middle" of the store and/or buy lots of things you weren't planning on buying before.
I've found they routinely have the best prices on the following:
Cheese - especially higher quality aged cheeses
Dish soap
sponges
Laundry detergent, dish soap, dish-washer soap, fabric softener etc
Nuts - especially walnuts, pecans and almonds
granola bars
dried fruit
Cat litter
Gasoline
Cream
Lettuce
Eggs
Bacon
Hot-dog-and-coke combo
whole rotisserie chickens
They usually have competitive prices (though not necessarily the absolute lowest) on
meat
seafood
milk
baked items (bread, muffins, cookies, .. most of which are just average in quality and not particularly healthy)
All their home-ware stuff (towels, sheets, clothes) in the middle of the store
Toilet paper, paper towels, napkins etc.
They don't have the best prices on:
tires
electronics & cameras
books and DVDs
.... a bunch of other random stuff like flooring and automotive/garage stuff. Shop around and know what a 'good' price is!