Look up a text value then count records < a date

G

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)
 
P

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
 
S

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top