Excel按指定月份统计整列含特定文本的单元格计数问题求解

Hey there, I totally get the frustration of trying to nail that count without adjusting the range every month—let's break down why your original formula didn't work and give you solid solutions that use whole columns.

Why Your COUNTIFS Attempt Failed #

The issue with COUNTIFS(MONTH(D:D), "=1", E:E, "Yelp") is that COUNTIFS expects its first argument for each condition to be a cell range, not an array of calculated values (like the output of MONTH(D:D)). It can't process the array that MONTH() returns, so it throws an error or gives incorrect results.

Solution 1: SUMPRODUCT (Works in All Excel Versions) #

This function is perfect for array-based calculations and handles whole-column references well. We'll add extra checks to ignore empty cells (so you don't count blank rows at the bottom of your sheet):

=SUMPRODUCT(--(MONTH(D:D)=1), --(E:E="Yelp"), --(D:D<>""), --(E:E<>""))

Let's break this down:

• --(MONTH(D:D)=1): Converts the boolean result (TRUE/FALSE) of "is this date in January?" into 1s and 0s.
• --(E:E="Yelp"): Does the same for "does this cell contain 'Yelp'?".
• --(D:D<>"") and --(E:E<>""): Ensures we don't count rows where either the date or source column is empty.
• SUMPRODUCT multiplies the values in each row together (only rows where all conditions are TRUE will give 111*1=1) and sums the total—giving you exactly the count you need.

Solution 2: COUNT + FILTER (Excel 365/2021 Only) #

If you have a newer Excel version with dynamic arrays, this is a cleaner option:

=COUNT(FILTER(E:E, (MONTH(D:D)=1) * (E:E="Yelp") * (D:D<>"") * (E:E<>""), ""))

• FILTER pulls only the E-column cells where all your conditions are met (using * to mean "AND" for array conditions).
• COUNT counts how many cells are returned by FILTER. The final "" tells Excel to return nothing instead of an error if there are no matches.

Pro Tip: Use Structured References (Even Better!) #

If you convert your data into an Excel Table (select your data > press Ctrl+T), you can use structured references that automatically expand as you add new rows every month. No more worrying about whole-column references picking up extra data:

Assuming your table is named PatientData, with columns Date (D) and Source (E):

=SUMPRODUCT(--(MONTH(PatientData[Date])=1), --(PatientData[Source]="Yelp"))

This is more reliable because it only includes rows in your table, not random empty cells below it.

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