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 column. Unfortunately the formula return errors.

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
      MVP
      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

         

Resources