Look up a text value then count records < a date
 

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)

Look up a text value then count records < a date
 

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

On Dec 8, 7:59*pm, GreenDriver
wrote:
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)

Look up a text value then count records < a date
 

=SUMPRODUCT((Sheet3!B2:B10000=A2)*(Sheet3!A2:A1000 0<=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" wrote:

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)

Look up a text value then count records < a date
 

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

"Sean Timmons" wrote:

=SUMPRODUCT((Sheet3!B2:B10000=A2)*(Sheet3!A2:A1000 0<=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" wrote:

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)