I like smaller big spaces but small houses and have thought about this a lot, so here are my observations:

I think 600-1400 is ideal for me and SO. The space must have lots of windows so it doesn't feel cramped. Our house is 1530 sq ft and is honestly too big, even with a dedicated office.

What regular and irregular uses will your house need to support? If you have out of town guests 4 weeks out of the year, you may not need a designated guest room, but you may need a room that can house a Murphy bed. I'd require 1 extra room if you need an office... having to commute due to lack of space is a horrifying idea.

I don't believe each person needs their own bedroom, I think 2 per BR is fine, but it's ideal to have a number of total separate spaces equal to the number of family members. If there are two adults and two kids in a 2br house, a family room plus one other room you can sit in alone (like a separate dining room) would likely suffice comfortably. Some of us just can't be around distractions (aka other people) and study or read.

More than square footage or number of bedrooms, it's about the use of space. A four foot wide hallway upstairs is pointless. An eat- in kitchen adjacent to a dining room just makes 2 smaller spaces, and you can only use one at a time anyway. An enormous bedroom has a ton of floor space, but you just need a small amount of floor space to add dressers, which is where your clothes should go (not strewn across the enormous floor). Many newer houses don't seem to understand these concepts as well as the smaller homes from the 60s and 70s.