COUNTIF / SUMPRODUCT with WEEKNUM

lukasz_rz

New Member
Joined
Oct 13, 2013
Messages
37
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,255
Office Version
365
Platform
Windows
How about
=SUMPRODUCT((YEAR(A2:A31)=2019)*(ISOWEEKNUM(A2:A31)=5))
 

lukasz_rz

New Member
Joined
Oct 13, 2013
Messages
37
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,255
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 13, 2013
Messages
37
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,255
Office Version
365
Platform
Windows
How did you change it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,255
Office Version
365
Platform
Windows
That should work fine.
Check that you don't have any #N/A values in col O
 

Forum statistics

Threads
1,077,856
Messages
5,336,794
Members
399,103
Latest member
PX04

Some videos you may like

This Week's Hot Topics

Top