Count items that match multiple criteria including date using WEEKNUM

I have attached a sample spreadsheet. On the List Summary worksheet, cell C4, I am trying to take a formula similar to B4:

Please Login or Register  to view this content.

The code in B4 should meet the following - right now, the count is off, and I don't know why

• Count all items in 'Master Matrix'!$B$7:$B$200 that match the initials in A4 (KP)
• Of the items above, how many do not have 'Received', 'Processed', or 'N/A' in 'Master Matrix'!$C$7:$C$200
• Of the items above, how many have a due date that is past

The code I want in C4 should meet the following.

• Count all items in 'Master Matrix'!$B$7:$B$200 that match the initials in A4 (KP)
• Of the items above, how many do not have 'Received', 'Processed', or 'N/A' in 'Master Matrix'!$C$7:$C$200
• Of the items above, how many have the same WEEKNUM as WEEKNUM(NOW()) --- this is so if an item is due on Wednesday, it doesn't matter if it is Monday or Friday of the same week, the value will be 1

The code I want in D4 should be

• Count all items in 'Master Matrix'!$B$7:$B$200 that match the initials in A4 (KP)
• Of the items above, how many do not have 'Received', 'Processed', or 'N/A' in 'Master Matrix'!$C$7:$C$200
• Of the items above, how many have the same WEEKNUM is <= (less than or equal to) WEEKNUM(NOW())

E4 should just be =SUM(B4:E4). Could someone help me with these formulas (no VBA)?

Tsjallie - Thanks for the info and the tip. The reason my range was to 200 was in case I added any additional lines. Got any tips on how I can not need to define the range with my formula (remember, I do have column headers of a few rows that need to be excluded)?

I didn't want to create an additional column, but in this instance, it's helpful. I'll just hide it. Also, went with your idea of a table.