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 2000
Excel 2000
^ these weeknum calculations aren't actually used and have to be deleted (I can't use a helper column in this case)
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 | |||||
---|---|---|---|---|---|
L | M | N | |||
1 | Week | Count | |||
2 | 22 | #VALUE! | #NUM! | ||
3 | 23 | ||||
4 | 24 | ||||
5 | 25 | ||||
6 | 26 | ||||
7 | 27 | ||||
8 | 28 | ||||
Analysis |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M2 | =SUMPRODUCT((WEEKNUM('Completed Rejects'!M$1:M$1000)=Analysis!L2)) | |
N2 | =COUNTIF(WEEKNUM('Completed Rejects'!M:M),Analysis!L2) |
Excel Workbook | |||||
---|---|---|---|---|---|
L | M | N | |||
1 | Responsible | Completion Date | |||
2 | Complete | 12/07/2010 | 29 | ||
3 | Complete | 30/06/2010 | 27 | ||
4 | Complete | 16/07/2010 | 29 | ||
5 | Complete | 15/07/2010 | 29 | ||
Completed Rejects |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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)