I am following the discussion by Burgess in https://arxiv.org/abs/1309.4133.
Let's suppose we have a theory of one massive particle coupled to gravity. The action is
$$
S = \int d^4 x \sqrt{-g} \left(\frac{M_{\rm Pl}^2}{2}\left(R - 2 \Lambda\right) -\frac{1}{2}(\partial \phi)^2 - \frac{1}{2} m^2 \phi^2\right)
$$
where $M_{\rm Pl}$ is the reduced Planck mass and $\Lambda$ is the cosmological constant. At this level, the cosmological constant is just some parameter of the Lagrangian.
Now we compute the effective field theory that governs physics on length scales of order the Hubble scale, $H_0$, or the length scale of the Universe today. Say that $\phi$ has a mass of order the electron mass. Then it will be much too massive to be relevant as a dynamical degree of freedom at such low energies (low energies = large distances in natural units where $\hbar=c=1$). However, it will renormalize the parameters in the low energy theory.
To account for this, we can compute the Wilsonian path integral by integrating out modes with energies above some cutoff $M_{\rm co} \sim H_0 \ll m$. There's a subtlety here that we assume we are working in Euclidean signature after doing a Wick rotation so that it even makes sense to separate high and low energy modes. Then the Wilsonian effective action $S_{\rm eff}$ is a functional of the metric and can be calculated via path integral
$$
e^{i S_{\rm eff}[g^{\rm l.e.}_{\mu\nu}]} = \int_{E > M_{\rm co}} D\phi Dg^{\rm h.e.}_{\mu\nu} e^{i S[g, \phi]}
$$
where $g^{\rm l.e.}_{\mu\nu}$ refers to modes with energies $\lesssim M_{\rm co}$ and $g^{\rm h.e.}_{\mu\nu}$ refers to modes with energies $\gtrsim M_{\rm co}$.
The point is then that the effective cosmological constant in the Wilson action, $\Lambda_W$, which is the quantity that governs the physics relevant for the Universe's expansion, will receive corrections from the scalar field due to threshold corrections when you match this low energy effective theory to yield the same predictions as the high energy one
$$
\Lambda_{\rm eff} \sim \Lambda + m^4
$$
And then the point is that the mass of any particle in the Standard Model gives a contribution to $\Lambda_{\rm eff}$ orders and orders of magnitude too large given what we observe. Even the electron's mass is way too big.
The answer to your question is that you can compute these threshold corrections (which is what Burgess does) and given the range of different unrelated masses in the Standard Model, they don't automatically cancel to give a small value like we observe. Adding supersymmetry doesn't help like it would in the Higgs case (if it were present) because supersymmetry has to be broken at a energy scale much larger than the Hubble scale to be compatible with observations.
Now there is no mathematical contradiction, since we can assume the fundamental parameter $\Lambda$ of the UV theory perfectly balances out all of those contributions from integrating out massive particles and has a small residual left over corresponding to the cosmological constant we observe. However, the fact that the effective field theory framework described above requires a delicate cancellation between parameters that are apparently independent strikes many physicists as highly bizarre, and points to something in the above story being wrong. Maybe our picture of the UV theory is incorrect, so $\Lambda$ and $m$ actually are related to some deeper parameter or symmetry that guarantees the near cancellation, maybe there's some dynamical mechanism that no one has been able to think of after a lot of work that forces $\Lambda_{\rm eff}$ to be small, maybe there's a landscape of universes and the anthropic principle means we find ourselves in a particular universe where this cancellation happens, maybe the entire effective field theory framework is misleading and needs to be replaced with something else, or, maybe Nature just has a bizarre coincidence and we have to deal with it.
