functions

[Dawn S]

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

 

[peter]

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(a4>aD1:a3,b1:b3))} this will
return a 6

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

peter

 

[Dawn S]

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

 

[Dawn S]

Sorry I was wrong in the last email if I was looking for A=1 and b>11/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

 

[RagDyeR]

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:A100>A1)*(B3:B100=B1))
--

HTH,

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

Sorry I was wrong in the last email if I was looking for A=1 and b>11/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]

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]

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

 

[Dawn S]

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

Thanks for you help

 

[Dawn S]

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

 

[RagDyeR]

Just *include* the other argument into the formula:
=SUMPRODUCT(--(A2:A999>AF2),--(A2:A999<AF3),--(B2:B999=AG2))

--

HTH,

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

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]

Thanks for the help, I figured it out 3 minutes before your response.

 

[CLR]

That's one of the beauties of this system..........."explaining your problem
so others can understand it enough to help you, often shows you the way to
get the answer yourself........"

Vaya con Dios,
Chuck, CABGx3

 

[Ragdyer]

Thanks for the feed-back.