尝试使用多个条件对 Excel 中的单元格进行计数 [英] Attempting to count cells in Excel using multiple criteria

问题描述

正如标题所述，我试图在单独的工作表上计算单元格范围，但似乎无法使其正常工作.任何帮助将不胜感激.

As the title states, I am trying to count across cell ranges on a separate sheet but cant seem to get it to work. Any help would be greatly appreciated.

=COUNTIFS(MONTH(original!A2:A58),"1",original!D2:D58,"=East")

推荐答案

您不能使用 MONTH 函数 作为 COUNTIFS 函数.您需要添加一个额外的计算级别，例如 SUMPRODUCT 函数.

You cannot use the MONTH function as a form of sub-function in a COUNTIFS function. You need to add an extra level of calculation with something like a SUMPRODUCT function.

=SUMPRODUCT((MONTH(original!A2:A58)=1)*(original!D2:D58="East"))

这应该计算原始工作表的 A 列中日期的月份是一月而相应行的 D 列是东的实例.

That should count the instances where the month of the date in the Original worksheet's column A is January and the corresponding row's column D is East.

如果您更喜欢使用 COUNTIFS 并且可以将日期保留为一年，您可以提供开始日期和结束日期，其中将特定年份的一月份括起来.

If you prefer to stay with a COUNTIFS and can keep the dates to a single year, you can provide a start date and end date which will bracket the month of January for a particular year.

=COUNTIFS(Original!A2:A58,">="&DATE(2015,1,1),Original!A2:A58,"<"&DATE(2015,2,1),Original!D2:D58,"East")

COUNTIFS 的默认比较运算符是 equals.无需输入.

The default comparison operator for COUNTIFS is equals. There is no need to type it in.

这篇关于尝试使用多个条件对 Excel 中的单元格进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！