Excel中按月统计指定单词出现次数的公式需求

Hey there! Let's figure out how to count how many times a specific word (like "car") shows up in Column B during specific months, using the dates in Column A (formatted as dd/mm/yyyy). I've got two reliable solutions for you, depending on your Excel version.

COUNTIFS is perfect here because it lets you count cells that meet multiple criteria.

统计单个月份（比如2月）的次数 #

Use this formula to count how many times "car" appears in February of the current year:

=COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),2,1),A:A,"<="&EOMONTH(DATE(YEAR(TODAY()),2,1),0),B:B,"car")

Let's break down what each part does:

• DATE(YEAR(TODAY()),2,1): Creates the first day of February for the current year (you can replace YEAR(TODAY()) with a specific year like 2024 if needed).
• EOMONTH(...,0): Gets the last day of the target month, so we cover the entire month's date range.
• B:B,"car": Ensures we only count cells in Column B that exactly match the word "car".

统计多个月份（2、3、4月）的总次数 #

To get the total count across February, March, and April, you can either add three separate COUNTIFS formulas together, or use a single formula that covers the full date range:

=COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),2,1),A:A,"<="&EOMONTH(DATE(YEAR(TODAY()),4,1),0),B:B,"car")

This formula counts all "car" entries between February 1st and April 30th of the current year.

If you're using an older Excel version that doesn't support COUNTIFS, SUMPRODUCT is your go-to. It works by multiplying boolean results (TRUE = 1, FALSE = 0) and summing the total.

统计单个月份（2月）的次数 #

=SUMPRODUCT((MONTH(A:A)=2)*(B:B="car"))

统计多个月份（2、3、4月）的总次数 #

=SUMPRODUCT((MONTH(A:A)>=2)*(MONTH(A:A)<=4)*(B:B="car"))

Pro tip: If Column A has blank cells, add a check to avoid errors:

=SUMPRODUCT((ISNUMBER(A:A))*(MONTH(A:A)>=2)*(MONTH(A:A)<=4)*(B:B="car"))

• If you want to count cells that contain "car" (like "blue car" or "car door"), use wildcards: replace "car" with "*car*" in any of the formulas.
• For better performance, replace full column references (like A:A) with a specific range (e.g., A2:A1000) that matches your data set.

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