Look up a text value then count records < a date

[GreenDriver]

In one worksheet, I have an table of data with header fields like:

Sheet3:
Date Created Room Number
1/1/09 11:30:02 AM 1234
2/26/09 17:29:04 PM 1234
4/2/09 12:02:04 AM 5678
6/7/09 16:24:03 PM 1234

In another worksheet, I have these headers:

Sheet1:
Room Number Date Treated #Created Before
1234 3/1/09

In Sheet1, I want to calculate in the "#Created Before" column how many rows
of data in Sheet3 meet the criteria of both the Room # listed in Sheet1 and
less than or equal to the Date Treated from Sheet3. In the above example, the
result would be 2. The dates in Sheet1 do come with the time included.

Ideally, I'd like to continue pasting data into Sheet3 and have the formulas
auto-update without having to change the "last cell" reference in the formula
(meaning, count entire columns as more data is added)

 

[Pete_UK]

Try this in C2 of Sheet1:

=SUMPRODUCT((INT(Sheet3!A$2:A$5000)<=B2)*(Sheet3!B$2:B$5000=B2))

You can't use full-column references with Sumproduct (unless you have
Excel 2007), but you could make those ranges almost a complete sheet:

=SUMPRODUCT((INT(Sheet3!A$2:A$65536)<=B2)*(Sheet3!B$2:B$65536=B2))

although this will increase the calculation time.

Hope this helps.

Pete

 

[Sean Timmons]

=SUMPRODUCT((Sheet3!B2:B10000=A2)*(Sheet3!A2:A10000<=B2))

Assuming you will have up to 9999 rows of data on sheet3. Adjust as needed.
You can have ranges past your last row of data, but you cannot use entire
column (A:A).

 

[GreenDriver]

Thanks so much, Sean. That seems too easy and it works. )