Count quantity by Week number

K

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,285
Office Version
  1. 365
Platform
  1. Windows
I have a list of 200 records.

I want to count the number of records per week number.

I have tried

=SUMPRODUCT(--(WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2))

Where WEEKNUM('Completed Rejects'!M$1:M$1000) is the list of dates

and Analysis!L2 is the week number (in a number format)

and I have tried

=COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2)



Sumproduct gives a #Value
Countif gives a #NUM!


Any help appreciated.
Excel Workbook
LMN
1WeekCount
222#VALUE!#NUM!
323
424
525
626
727
828
Analysis
Excel 2000
Cell Formulas
RangeFormula
M2=SUMPRODUCT((WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2))
N2=COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2)
Excel Workbook
LMN
1ResponsibleCompletion Date
2Complete12/07/201029
3Complete30/06/201027
4Complete16/07/201029
5Complete15/07/201029
Completed Rejects
Excel 2000
Cell Formulas
RangeFormula
N2=WEEKNUM(M2)
N3=WEEKNUM(M3)
N4=WEEKNUM(M4)
N5=WEEKNUM(M5)



^ these weeknum calculations aren't actually used and have to be deleted (I can't use a helper column in this case)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Which version of Excel are you using?

In Excel 2003 and earlier WEEKNUM won't accept a range (that's typical of Analysis ToolPak functions).

In Excel 2007 there's a workaround whereby you convert the range to an array, i.e.

=SUMPRODUCT((WEEKNUM('Completed Rejects'!M$1:M$1000+0)=Analysis!L2)+0)

Note the +0

That should work in Excel 2007/2010 as long as no M1:M1000 entries are text (if you have a header in M1 then change the range).

Alternatively you should use a helper column to generate the week number and then use COUNTIF
 
Upvote 0
OK, I see you don't want a helper column......

You can use this formula as an equivalent to =WEEKNUM(A2)

=INT((A2-DATE(YEAR(A2),1,1)-WEEKDAY(A2))/7)+2

so in any version of Excel you can use that construction instead of WEEKNUM within SUMPRODUCT, i.e.

=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L2)+0)
 
Upvote 0
This will be used in Xl 2000, 2003 & 2007

Thanks for the reply.

You are right

=INT((A2-DATE(YEAR(A2),1,1)-WEEKDAY(A2))/7)+2 - WORKS

However

=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L2)+0) Still gives a #VALUE
Excel Workbook
LM
1WeekCount
222#VALUE!
323#VALUE!
424#VALUE!
525#VALUE!
Analysis
Excel 2000
Cell Formulas
RangeFormula
M2=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L2)+0)
M3=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L3)+0)
M4=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L4)+0)
M5=SUMPRODUCT((INT(('Completed Rejects'!M$1:M$1000-DATE(YEAR('Completed Rejects'!M$1:M$1000),1,1)-WEEKDAY('Completed Rejects'!M$1:M$1000))/7)+2=Analysis!L5)+0)
 
Upvote 0
If I remove the range it works fine.
Excel Workbook
LM
7270
8280
9291
10300
Analysis
Excel 2000
Cell Formulas
RangeFormula
M7=SUMPRODUCT(--(INT(('Completed Rejects'!$M$2-DATE(YEAR('Completed Rejects'!$M$2),1,1)-WEEKDAY('Completed Rejects'!$M$2))/7)+2=L7))



Not sure what it means though.
 
Upvote 0
Again if you have any text in 'Completed Rejects'!M$1:M$1000, i.e. a header in M1 (or even "formula" blanks) you'll get an error with that formula.

Alternatively you could just use the start date of each week so if L2 contains that date rather than a week number you could use

=SUMPRODUCT(('Completed Rejects'!M$1:M$1000>=L2)*('Completed Rejects'!M$1:M$1000< L2+7))
 
Upvote 0
Sorry missed the comment about the header. Changed to M2:M1000

Works perfect now - Thanks
 
Upvote 0
Probably could but I've always managed to avoid pivot tables in the past. Really need to learn more about them
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
973
Armstrong_N14
A
A
  • Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
256
amolvijay
A
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
443
KRice
K
SaraWitch
  • Solved
Join text and return dates where there is one but ignore if empty cells
2
Replies
12
Views
561
SaraWitch
SaraWitch
M
  • Solved
Counting how much number is in ascending order in a row.
2
Replies
12
Views
373
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,045
Messages
6,122,840
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top