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