Excel COUNTIFS计数月份在日期范围内 [英] Excel COUNTIFS count months in date range

问题描述

我正在使用 = COUNTIFS（）函数来计算满足两个条件的值的数量。

  = COUNTIFS（'2014'！$ F：$ F，B3，'2014'！$ H：$ H，A13）
 但是，我不想也要包括特定的月份作为条件，但日期的范围是格式，3/11/2014。我已经查看了几种不同的方法，可以使用 = SUMPRODUCT（）和 MONTH 公式，但无法找到适用于 COUNTIFS 函数的功能，以满足原始条件。
 
 
 
我甚至试图通过使用通配符来考虑盒子外，
  = COUNTIFS（'2014'！$ F ：$ F，B3，'2014'！$ H：$ H，A13，'2014'！$ A：$ A，* / 11/2014）
   
 
 
到 = COUNTIFS（）函数？
 
 
 
编辑* 
 
 
 
我有一个临时解决方案，这不是一个确定的解决方案，但是我创建了一个帮助列，包含
  = TEXT （A1，DD / MM / YYYY）
  
 
  = COUNTIFS（'2014 ！$ F：$ F，B3，'2014'！$ H：$ H，A13，'2014'！$ I：$ I，* / 11/2014）
  
然而，仍然有点少一点的宝贝！
解决方案
 p>由于您似乎所有您的excel日期设置为格式 dd / mm / yyyy 此公式应该工作：
  = COUNTIFS（'2014'！$ F：$ F，B3，'2014'！$ H：$ H，A13，'2014 '$ A：$ A，> =& EOMONTH（DATE（2014,10,1），0）+ 1，'2014'！$ A：$ A，& EOMONTH （2014,11,1），0）+1）
  
的整个月。所有日期大于或等于所需月份的开始，所有日期小于下个月的开始。
 
 
 
要更改月份：
  = COUNTIFS（'2014'！$ F：$ F，B3，'2014 $ H：$ H，A13，'2014'！$ A：$ A，> =& EOMONTH（DATE（2014,10,1），0）+ 1，'2014'！$ A： $ A，&EOMONTH（DATE（2014,11,1），0）+1）
 ^^更改10到一个月前^^更改11到您想要的月份。 
  
 
I'm currently using the =COUNTIFS() function to count the number of values that satisfy two conditions.
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13)
However I no want to also include specific months as a condition, but the range of dates is in the format, 3/11/2014. I have looked up a few different ways that can count specific months from a list of dates using a combination of the =SUMPRODUCT() and MONTH formulas, but unable to find one that works in a COUNTIFS function to satisfy the original conditions also.

I even tried to think outside of the box by using wild cards,
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, "*/11/2014")
Alas to no avail.

Does anyone know if there is a way in which to include counting specific months to a =COUNTIFS() function?

EDIT*

I have a temporary fix, thats not a sure fire solution, but I have created a helper column containing 
=TEXT(A1, "DD/MM/YYYY")
And tied this in with my original wild card solution
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$I:$I, "*/11/2014")
however still after something a little less bodgy!
解决方案
Since it seems that all your dates for excel are set to the format of dd/mm/yyyy this formula should work:
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, ">="&EOMONTH(DATE(2014,10,1),0)+1,'2014'!$A:$A,"<"&EOMONTH(DATE(2014,11,1),0)+1)
This basically checks the column against the span of the entire month.  All dates greater or equal to the start of the desired month, and all dates less than the start of the next month.

To change the month:
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, ">="&EOMONTH(DATE(2014,10,1),0)+1,'2014'!$A:$A,"<"&EOMONTH(DATE(2014,11,1),0)+1)
                                                                                    ^^ change 10 to one month prior               ^^ change 11 to the month you want.


                        
这篇关于Excel COUNTIFS计数月份在日期范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！