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

问题描述

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

  = COUNTIFS（MONTH（original！A2：A58），1，original！D2 ：D58，=东）
  

解决方案

MONTH功能作为 COUNTIFS功能中的子功能形式。您需要添加额外的计算水平，类似于 SUMPRODUCT函数。

  = SUMPRODUCT（（MONTH（original！A2：A58）= 1） *（原来的D2：D58 =东））
  

原始工作表的列A中的日期为1月，相应行的列D为东。

如果您希望保留COUNTIFS，并将日期保留为单年，您可以提供一个开始日期和结束日期，该日期和结束日期将包括特定年份的1月份。

  = COUNTIFS ！（原A2：A58， > = &安培; DATE（2015,1,1），原始A2：！A58， < &安培; DATE（2015,2,1），原始D2：D58， 东）
  

COUNTIFS的默认比较运算符为等于。没有必要输入。

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")

解决方案

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"))

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.

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")

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

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