Formula help: Count if between 2 dates and if not dup-XL2007

[Asher]

What I have right now:

1. 12 Tables on 12 different sheets with data connected to an Access source (queries)
2a. Each Table has a list of information with Id numbers in the first column (Report).
2b. These numbers can be duplicate (multi to multi join issues)
3. There is also a date column (Due Date) in each table.
4. There is a worksheet for statistics (Stats).
5. The Stats worksheet has a table-like structure (not an actual list object) that is supposed to identify the number of items with a due date between or equal to a certain date (by week).
It is structured like this:

The top row lists the title (first column) and the weeks (calculation columns).
The first column lists the tables it it drawing from.
The formulas in the cells are supposed to count the number of items per table that are on or before the week in the formula's column and after the week prior AND if the Report number is duplicated, only count it once.

The formula is as follows (with Excel Table naming for dynamic flexibility):

{=COUNT(IF((Table_tblSTUFF[Due Date]>B$18)*(Table_tblSTUFF[Due Date]<=C$18)*(Table_tblSTUFF[[#Data],[#Totals],[Report]]<>Table_tblSTUFF[[#Headers],[#Data],[Report]]),1))}

***without Table Naming it is this: {=COUNT(IF((STUFF!$G$4:$G$103>B$19)*(STUFF!$G$4:$G$103<=C$19)*(STUFF!$A$4:$A$103<>STUFF!$A$3:$A$102),1))} *** but I need the Excel 2007 Table Naming because when the table grows or shrinks beyond/below 103 rows, I need the formula to dynamically move with it***

This formula takes a matrix approach, and it actually works in ALMOST all cases. The problem arises, when there is a table that has only 1 entry. For all others the formula ends up multiplying large enough matrices {0,1,1,0}*{1,1,1,1,1} that a true number comes up. but with only 1 entry, it ends up lie this {1}*{1,1} which shows 2 instead of 1.

Any ideas how to fix that?

let em know if you need me to attach an example.

[HansV]

A stripped down sample workbook would be helpful.

[Asher]

Here is the stripped down copy.

[HansV]

I don't understand why your formula is so complex. How about this in B3:

=SUMPRODUCT((Table_tblSTUFF[Due Date]>A$2)*(Table_tblSTUFF[Due Date]<=B$2))

Copy and paste (or paste special > formulas) to C3:AU3.

[Asher]

The last part of the formula (the part that makes it complex...(Table_tblSTUFF[[#Data],[#Totals],[Report]]<>Table_tblSTUFF[[#Headers],[#Data],[Report]])) is to ensure there are no duplicates. I guess I forgot to add duplicate Report numbers in the example copy.

Do you know a simpler way to do that? Or, does the formula you gave me do that?

[HansV]

So two identical ID numbers with a due date during the same period should count as one?

[Asher]

Correct.

[HansV]

Your test for uniqueness doesn't work - it only checks whether an ID is equal to its neighbor.

Use this array formula (confirm with Ctrl+Shift+Enter) in Stats!B3:

=IFERROR(SUM(IF(FREQUENCY(IF((Table_tblSTUFF[Due Date]>A$2)*(Table_tblSTUFF[Due Date]<=B$2),Table_tblSTUFF[Report]),IF((Table_tblSTUFF[Due Date]>A$2)*(Table_tblSTUFF[Due Date]<=B$2),Table_tblSTUFF[Report])),1)),0)

It can be copied to C3:AU3. The IFERROR makes the formula return 0 if there is only one ID and no match.

[Asher]

hmm... for some reason I got it to work on the stripped down copy I attached, but not on the true workbook... I can't figure out why. Everything should be the same....

[HansV]

Different table names? Different field names? Forgot to confirm with Ctrl+Shift+Enter?