基于B1日期，用单公式统计C-F列20岁及以下儿童数量（无额外列）

Absolutely, you can pull this off with a single formula using SUMPRODUCT—it’s ideal for handling multi-range conditions without adding extra columns. Here’s how to make it work:

Recommended Formula #

=SUMPRODUCT(--(C:F<>""), --(C:F<=B1), --(INT(YEARFRAC(C:F, B1, 1))<=20))

How It Breaks Down #

Let’s walk through each part so you understand what’s happening:

• --(C:F<>""): Turns empty cells into 0 and cells with birthdays into 1 (we only want to count valid entries).
• --(C:F<=B1): Filters out any birthdays that fall after the date in B1—since future birthdays can’t calculate a valid age for a child alive today.
• --(INT(YEARFRAC(C:F, B1, 1))<=20):
  • YEARFRAC(...) calculates the exact fractional years between each birthday and the date in B1 (using actual day counts for accurate age).
  • INT() converts that fraction to a whole number (the child’s real age).
  • The <=20 check flags ages that meet your criteria, and -- turns those TRUE/FALSE results into 1s and 0s.

SUMPRODUCT multiplies these three sets of values together and sums the total—every cell that passes all three checks adds a 1 to your final count.

Alternative Formula (Using DATEDIF) #

If you prefer using DATEDIF for age calculations, this version handles future birthdays by treating them as an age over 20 (so they don’t get counted):

=SUMPRODUCT(--(C:F<>""), --(IFERROR(DATEDIF(C:F, B1, "Y"), 21)<=20))

• DATEDIF(C:F, B1, "Y") gives the number of complete years between each birthday and B1.
• IFERROR(..., 21) replaces errors (from future birthdays) with 21, ensuring those entries are excluded from the count.

Quick Notes #

• This works in all modern Excel versions (365, 2021, 2019, and even older releases).
• No need for array formula shortcuts—just press Enter, and SUMPRODUCT handles the array logic on its own.

内容的提问来源于stack exchange，提问作者Hery0502