Forum Discussion

Syed Ibrahim's avatar
Syed Ibrahim
Copper Contributor
Jul 26, 2017

COUNTIFS WITH month formula for criteria range

Hi I am getting error when I used the formula in the range as follow   COUNTIFS(MONTH('CR List'!$B:$B),"=1",'CR List'!$P:$P,D$14)   I am trying to get the month of jan for a range with date colum...
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jul 26, 2017

Syed,

 

criteria range has to be a reference not just an array.

 

Workaround 1: Add a helper column with MONTH(P1) and refer to it:

=COUNTIFS('CR List'!$Z:$Z),1,'CR List'!$P:$P,D$14)

Workaround 2: If your dates are from year 2017:

COUNTIFS('CR List'!$B:$B),">=01.01.2017",'CR List'!$B:$B),"<=31.01.2017",'CR List'!$P:$P,D$14)
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 26, 2017

    Workaround 3:

    =SUMPRODUCT( (MONTH('CR List'!$B:$B)=1)*(CR List'!$P:$P = D$14))

     

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Jul 26, 2017

      Sergej,

       

      thanks for adding. I was focussed on COUNTIFS() and totally forgot SUMPRODUCT().

       

      And while in a broader perspective we could add:

      Workaround 4: Pivot table

      Workaround 5: Power Query

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 26, 2017

        Detlef,

         

        Perhaps FREQUENCY could be adjusted for this as well, i played a bit, could be like

        =SUM(FREQUENCY(IF((MONTH('CR List'!$B:$B)=1)*('CR List'!$P:$P=D$14),ROW('CR List'!B:B),FALSE),IF((MONTH('CR List'!$B:$B)=1)*('CR List'!$P:$P=D$14),FALSE,ROW('CR List'!B:B))))

        (array formula),

        but i belive it'll be veeeery slow.

         

         

Resources