In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer would be 5.

Please help with the formula, I have tried countif and nested in countif.

Thanks
Dawn

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.

I do not want to sum the 1,2,3. I want to count each one as 1, the answer
would be 3.

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.

Hi,
try this...
=countif(a1:a3,"" & a4)
peter

-----Original Message-----
I do not want to sum the 1,2,3. I want to count each one
as 1, the answer
would be 3.

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a
specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the
answer
would be 5.

Please help with the formula, I have tried countif
and
nested in countif.

Thanks
Dawn
.


.

Hi,
sorry, I didn't see your other post.

see if this is what you need...

=SUM(IF( ( $a$1:$a$3$a$4)* ($b$1:$b$3=$b$4 ) ,1,0))

a4 has a date criteria
b4 has a criteria that must match items in b

peter
-----Original Message-----
I do not want to sum the 1,2,3. I want to count each one
as 1, the answer
would be 3.

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a
specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the
answer
would be 5.

Please help with the formula, I have tried countif
and
nested in countif.

Thanks
Dawn
.


.

Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04

a b
1 12/30/04
1 11/15/04
1 12/15/04
2 12/1/04

With this my answer would be 2

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.

It appears that you've switched your dates from ColumnA to ColumnB in your
two examples.

Anyway, try this:

Dates in ColumnA, from A3 to A100.
Values in ColumnB, from B3 to B100

Column headers in A2 and B2.

Enter the specific start date in A1,
And the value to find (count) in B1.

Then, try this:

=SUMPRODUCT((A3:A100A1)*(B3:B100=B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Dawn S" wrote in message
...
Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04

a b
1 12/30/04
1 11/15/04
1 12/15/04
2 12/1/04

With this my answer would be 2

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.

Actually this is what worked:
=SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2))

Thanks for you help


"RagDyeR" wrote:

It appears that you've switched your dates from ColumnA to ColumnB in your
two examples.

Anyway, try this:

Dates in ColumnA, from A3 to A100.
Values in ColumnB, from B3 to B100

Column headers in A2 and B2.

Enter the specific start date in A1,
And the value to find (count) in B1.

Then, try this:

=SUMPRODUCT((A3:A100A1)*(B3:B100=B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Dawn S" wrote in message
...
Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04

a b
1 12/30/04
1 11/15/04
1 12/15/04
2 12/1/04

With this my answer would be 2

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.

Ok, one more question. Take the same formula and instead of AF3 (which is a
date) I need between AF2 and AF3. (AF2 & <AF3

Thanks
Dawn

"Dawn S" wrote:

Actually this is what worked:
=SUMPRODUCT(--(A2:A999AF3),--(B2:B999=AG2))

Thanks for you help


"RagDyeR" wrote:

It appears that you've switched your dates from ColumnA to ColumnB in your
two examples.

Anyway, try this:

Dates in ColumnA, from A3 to A100.
Values in ColumnB, from B3 to B100

Column headers in A2 and B2.

Enter the specific start date in A1,
And the value to find (count) in B1.

Then, try this:

=SUMPRODUCT((A3:A100A1)*(B3:B100=B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Dawn S" wrote in message
...
Sorry I was wrong in the last email if I was looking for A=1 and b11/30/04

a b
1 12/30/04
1 11/15/04
1 12/15/04
2 12/1/04

With this my answer would be 2

"peter" wrote:

Hi,
Try this...
a b
1 1/1/2005 1
2 1/3/2005 2
3 1/6/2005 3

4 1/7/05 {=SUM(IF(a4aD1:a3,b1:b3))} this will
return a 6

make sure to use the cntl shift enter combination after
writing the formula.

peter

-----Original Message-----
In column A are dates
In column B are values (1, 2, 3)
I want to look at A for a date greater than a specific
date then match to B
for a specific number and count as 1 if they match

if A 11/30/04 and B = 1 then count as 1
If I have 5 rows that fit the criteria then the answer
would be 5.

Please help with the formula, I have tried countif and
nested in countif.

Thanks
Dawn
.