A useful sufficient condition is that such a functor $F : C \to \text{Set}$ is representable; such functors preserve limits more or less by definition. For example:
- The forgetful functor $\text{Grp} \to \text{Set}$ preserves limits because it's $\text{Hom}(\mathbb{Z}, -)$.
- The forgetful functor $\text{Ring} \to \text{Set}$ preserves limits because it's $\text{Hom}(\mathbb{Z}[x], -)$.
- For $R$ a commutative ring, the forgetful functor $R\text{-Mod} \to \text{Set}$ preserves limits because it's $\text{Hom}(R, -)$.
- The forgetful functor $\text{Top} \to \text{Set}$ preserves limits because it's $\text{Hom}(\bullet, -)$ where $\bullet$ is the one-point space.
- The homotopy group functors $\pi_k : \text{hTop}_{\ast} \to \text{Set}$, where $\text{hTop}_{\ast}$ is the homotopy category of pointed topological spaces, preserve limits because they are $\text{Hom}(S^k, -)$.
(This argument doesn't directly apply to functors which take values in categories other than $\text{Set}$, but there's a way to extend it that I'm not familiar with: see this MO question. The extended argument should handle cohomology by Brown representability.)
Another useful sufficient condition is that such a functor $F : C \to D$ is a right adjoint (equivalently, has a left adjoint). This is true of many forgetful functors (where the left adjoint is the corresponding free functor), including the ones above. In fact, the forgetful functor $\text{Top} \to \text{Set}$ has both a left and a right adjoint, which is why it preserves both limits and colimits. The left adjoint sends a set to the discrete topology on that set, and the right adjoint sends a set to the indiscrete topology on that set.
The two conditions are related. If $F : C \to \text{Set}$ has a left adjoint $G : \text{Set} \to C$, then
$$\text{Hom}_C(G(X), Y) \cong \text{Hom}_{\text{Set}}(X, F(Y))$$
implies that
$$\text{Hom}_C(G(1), Y) \cong \text{Hom}_{\text{Set}}(1, F(Y)) \cong F(Y)$$
hence $F$ is representable by $G(1)$. This general pattern explains the forgetful examples above.
As k.stm says in the comments, usually a more general thing is true: these categories $C$ are equipped with forgetful / underlying set functors $U : C \to \text{Set}$ which tend to have a left adjoint, the "free" functor $F : \text{Set} \to C$. Whenever this is true, it follows that $U$ preserves all limits, not just products.
Sometimes, but more rarely, $U$ will also have a right adjoint, which will give a "cofree" functor. Whenever this is true, it follows that $U$ preserves all colimits, not just coproducts. This happens, for example, when $C = \text{Top}$: here the left adjoint equips a set with the discrete topology and the right adjoint equips a set with the indiscrete topology. But it doesn't happen for, say, groups or rings.
So one way to reformulate your question is:
Why do the forgetful functors $U : C \to \text{Set}$ we write down tend to have left adjoints, but not right adjoints? Equivalently, why are there usually free structures, but not usually cofree structures?
A rough answer is that we can expect "free" structures whenever the structure is described by operations satisfying equational axioms, since then we can build free objects by applying all possible operations modulo all axioms. On the other hand, operations also make it difficult for the forgetful functor to preserve coproducts, since in a coproduct of two structures you can apply operations to elements of both structures, so you'll usually get something bigger than the disjoint union.
(Dually, you should expect "cofree" structures whenever the structure is described by "co-operations," and this does in fact happen: for example, the forgetful functor from coalgebras to vector spaces has a right adjoint but not a left adjoint, called the cofree coalgebra.)
A more precise answer would invoke, say, the machinery of Lawvere theories, which among other things has the benefit of also telling you exactly what colimits you can expect these forgetful functors $U$ to preserve. This is a long story so I don't want to get into it unless you feel like it really answers your question, but the gist is that Lawvere theories present familiar structures like groups, rings, and modules in a way that fundamentally uses finite products, but nothing else. You can deduce from this that the forgetful functor $U$ preserves (and in fact creates) any limits or colimits that commute with finite products in $\text{Set}$. Every limit commutes with finite products, and the colimits that commute with finite products in $\text{Set}$ are precisely the sifted colimits. These include, for example, increasing unions, which is an abstract way to see why the set-theoretic increasing union of a sequence of groups is still a group, and the same with groups replaced by rings, modules, etc.
Best Answer
A rigorous proof that the infinite product of countably many 2-point spaces $A_n = \{0,1\}$ does not exist: (Really, just an elaboration of Martin's comment.)
Assume that $P$ is such a product, then $P$ must be $\prod_n A_n$ as a set. Let $e_n$ be the distance (in $P$) between the $0$-function and the function $x_n$ that takes the value $1$ only in the $n$-th place. The sequence $(e_n)$ must converge to $0$, because the $x_n$ will converge to the $0$ function. (They must converge by compactness, and the continuity of the projections means that all entries of the limit must be $0$.) [EDIT: As Martin Brandenburg pointed out, compactness is not enough to show convergence of the whole sequence, but only of some subsequence $(e_{n_i})$. For notational simplicity, assome $n_i=i$ for all $i$; otherwise restrict attention from $(x_n)$, $(e_n)$, $(d_n)$ to the respective subsequences $(x_{n_i}$, $(e_{n_i})$, $(d_{n_i})$ from now on.]
Let $(d_n)$ be a sequence of real numbers converging to $0$, but more slowly than $(e_n)$, i.e., $e_n = o(d_n)$. (E.g., $e_n=\sqrt{d_n}$.) Let $Q = \prod A_n$, but with the following metric: $d(x,y) = e_n$, if $x$ and $y$ agree on the first $n$ values, but not on the next one. Project $Q$ to each $A_n$ $-$ these maps are Lipschitz-continuous. So they factor through a map from $Q$ to $P$. Now this map must be the identity (apply the forgetful functor); but points with distance $e_n$ are then mapped to points with distance $d_n$, which is not possible for a Lipschitz function.