Forum Discussion
Syed Ibrahim
Jul 26, 2017Copper Contributor
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...
SergeiBaklan
Jul 26, 2017Diamond Contributor
Workaround 3:
=SUMPRODUCT( (MONTH('CR List'!$B:$B)=1)*(CR List'!$P:$P = D$14))
Detlef_Lewin
Jul 26, 2017Silver Contributor
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
- SergeiBaklanJul 26, 2017Diamond Contributor
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.