COUNTIF / SUMPRODUCT with WEEKNUM

[lukasz_rz]

Hello Guys.

A quick and simple question (too difficult for me though).

I have a column with dates (Column A). I need to count the number of records that are from the certain year (YEAR formula) and certain week (WEENNUM) without using any additional calculation columns (file is big, a lot of records, don't want to slow it down more).

I've tried with COUNTIF, but what I've read is that it won't work. I've tried SUMPRODUCT, but it is not working for me either.

Please help. I appreciate.

 

[Fluff]

How about
=SUMPRODUCT((YEAR(A2:A31)=2019)*(ISOWEEKNUM(A2:A31)=5))

 

[lukasz_rz]

How about
=SUMPRODUCT((YEAR(A2:A31)=2019)*(ISOWEEKNUM(A2:A31)=5))

Thanks. It works however when I take whole column as the range, I get #VALUE error. The thing is that the range might not be constant, so what should I do to adjust to that?

 

[Fluff]

Firstly NEVER use whole columns in an array formula, it can seriously slow down your workbook.
Decide what the max range is likely to be & add a small margin for error. So if you think that the max number of rows will be 1,000 use something like A2:A2000
I suspect that you have text values somewhere in the range, which would give you the #value error

 

[lukasz_rz]

Firstly NEVER use whole columns in an array formula, it can seriously slow down your workbook.
Decide what the max range is likely to be & add a small margin for error. So if you think that the max number of rows will be 1,000 use something like A2:A2000
I suspect that you have text values somewhere in the range, which would give you the #value error

You are totally right.
Yes, the problem was with the first row, which is a text format I will follow your advice.
Additional question: what if I want to add the next condition which is simply: the cell in column B = "X". I've received #N/A error this time, when I've tried.

 

[Fluff]

How did you change it?

 

[lukasz_rz]

How did you change it?

=SUMPRODUCT((YEAR('Rawdata'!D2:D20000)=$A$20)*(ISOWEEKNUM('Rawdata'!D2:D20000)=$A$21)*('Rawdata'!O2:O20000="X"))

 

[Fluff]

That should work fine.
Check that you don't have any #N/A values in col O