I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.

Try this:

=SUMPRODUCT((B1:B100<=DATE(2008,7,31))

I've assumed your dates are in B1:B100 - adjust to suit.

Hope this helps.

Pete

On Nov 16, 7:18 pm, Dan Soleau <Dan
wrote:
I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.

That didn't work. what i have is a tracker setup like this

Dan 1-1-07
Dan 1-3-07
Pete 1-3-07
Pete 1-3-07
Pete 1-1-07

I want to know, how many dates are there less than 1-3-07 for Dan and then
also for pete. So I will have to use a formula that has both columns
involved.
"Pete_UK" wrote:

Try this:

=SUMPRODUCT((B1:B100<=DATE(2008,7,31))

I've assumed your dates are in B1:B100 - adjust to suit.

Hope this helps.

Pete

On Nov 16, 7:18 pm, Dan Soleau <Dan
wrote:
I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.

With data similar to this in columns A and B:
contractor 1 8/17/2008
contractor 2 8/12/2008
contractor 3 8/7/2008
contractor 1 8/2/2008
contractor 2 7/28/2008
contractor 3 7/23/2008
contractor 1 7/18/2008
contractor 2 7/13/2008
contractor 3 7/8/2008
contractor 1 7/3/2008
contractor 2 6/28/2008
contractor 3 6/23/2008

If you want an individual count for each contractor, the easiest way is to
create a separate table with a list of contractors (the sample was entered in
columns E and F) and use the following formula to the right of the contractor
name to count dates before 7/30/2008.
=SUMPRODUCT(($A$2:$A$19=E2)*($B$2:$B$19<=DATE(2008 ,7,31)))
You can enter for the first contractor and then copy down for additional.
The results from the above table would be:
contractor 1 2
contractor 2 3
contractor 3 3



"Dan Soleau" wrote:

I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.

Your requirement wasn't that clear in your first posting. Put Dan in
D1 and Pete in D2 and put this formula in E1:

=SUMPRODUCT((B$1:B$100<=DATE(2008,7,31)*(A$1:A$100 =D1))

Copy it into D2 (or further down if you have other names in column D).

Hope this helps.

Pete

On Nov 16, 7:46 pm, Dan Soleau
wrote:
That didn't work. what i have is a tracker setup like this

Dan 1-1-07
Dan 1-3-07
Pete 1-3-07
Pete 1-3-07
Pete 1-1-07

I want to know, how many dates are there less than 1-3-07 for Dan and then
also for pete. So I will have to use a formula that has both columns
involved.



"Pete_UK" wrote:
Try this:

=SUMPRODUCT((B1:B100<=DATE(2008,7,31))

I've assumed your dates are in B1:B100 - adjust to suit.

Hope this helps.

Pete

On Nov 16, 7:18 pm, Dan Soleau <Dan
wrote:
I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.- Hide quoted text -

- Show quoted text -

What if I want to leave dan and pete in the same column and I basicaly want
to set up a report like this on another worksheet

Dan 1
Pete 1

Assuming that there was only one date that was less than the 1-3-07

"Pete_UK" wrote:

Your requirement wasn't that clear in your first posting. Put Dan in
D1 and Pete in D2 and put this formula in E1:

=SUMPRODUCT((B$1:B$100<=DATE(2008,7,31)*(A$1:A$100 =D1))

Copy it into D2 (or further down if you have other names in column D).

Hope this helps.

Pete

On Nov 16, 7:46 pm, Dan Soleau
wrote:
That didn't work. what i have is a tracker setup like this

Dan 1-1-07
Dan 1-3-07
Pete 1-3-07
Pete 1-3-07
Pete 1-1-07

I want to know, how many dates are there less than 1-3-07 for Dan and then
also for pete. So I will have to use a formula that has both columns
involved.



"Pete_UK" wrote:
Try this:

=SUMPRODUCT((B1:B100<=DATE(2008,7,31))

I've assumed your dates are in B1:B100 - adjust to suit.

Hope this helps.

Pete

On Nov 16, 7:18 pm, Dan Soleau <Dan
wrote:
I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.- Hide quoted text -

- Show quoted text -

I purposefully used the DATE function so you can easily change it if
you wanted to - the parameters are year, month and day in that order.
An alternative is to put a date into a cell somewhere, and use a
reference to that cell. Assuming that your original data remains on
Sheet1 when you put Dan and Pete in A1 and A2 of Sheet2, then the
formula becomes this in B1 of Sheet2:

=SUMPRODUCT((Sheet1!B$1:B$100<=DATE(2007,3,1)*(She et1!A$1:A$100=A1))

and this can be copied down as required. Don't forget to adjust the
range to suit your real data, if you have more than 100 rows. I've
changed the date to 1st March 2007 as requested.

Hope this helps.

Pete

On Nov 16, 8:07 pm, Dan Soleau
wrote:
What if I want to leave dan and pete in the same column and I basicaly want
to set up a report like this on another worksheet

Dan 1
Pete 1

Assuming that there was only one date that was less than the 1-3-07



"Pete_UK" wrote:
Your requirement wasn't that clear in your first posting. Put Dan in
D1 and Pete in D2 and put this formula in E1:

=SUMPRODUCT((B$1:B$100<=DATE(2008,7,31)*(A$1:A$100 =D1))

Copy it into D2 (or further down if you have other names in column D).

Hope this helps.

Pete

On Nov 16, 7:46 pm, Dan Soleau
wrote:
That didn't work. what i have is a tracker setup like this

Dan 1-1-07
Dan 1-3-07
Pete 1-3-07
Pete 1-3-07
Pete 1-1-07

I want to know, how many dates are there less than 1-3-07 for Dan and then
also for pete. So I will have to use a formula that has both columns
involved.

"Pete_UK" wrote:
Try this:

=SUMPRODUCT((B1:B100<=DATE(2008,7,31))

I've assumed your dates are in B1:B100 - adjust to suit.

Hope this helps.

Pete

On Nov 16, 7:18 pm, Dan Soleau <Dan
wrote:
I have two columns. One contains general contractor names the other contains
dates from current until december of 2008. I want to create an updating
formula to count how many dates are less than or equal too 7/31/08 for each
contractor.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -