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