Search for a value in column A and return the vaule/values in column B to column C

[minismood]

Hello!

This is what my worksheet looks like:

Column A (names of people):
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Persson, Palle
Persson, Palle
Persson, Palle
Persson, Palle
Persson, Palle
and so forth...

Column B (dates):
2005-06-15
2005-07-13
2005-08-15
2005-05-24
and so forth...

I want to know if there´s a formula (of course there is! where I
can search for a value (date) in column B that is older then let say
2005-08-01 and find out how many dates are older than 2005-08-01 for
i.e Persson, Palle.

I hope I´ve made myself clear.

Ps. The date, i.e. 2005-08-01, is how me write it in Sweden.

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(--($A$2:$A$100="Persson,
Palle"),--($B$2:$B$100>DATE(2005,8,1)))

better still put the name required in say C1 and Date required in D1
then
=SUMPRODUCT(--($A$2:$A$100=C1),--($B$2:$B$100>D1))


--
Regards

Roger Govier


Hello!

This is what my worksheet looks like:

Column A (names of people):
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Persson, Palle
Persson, Palle
Persson, Palle
Persson, Palle
Persson, Palle
and so forth...

Column B (dates):
2005-06-15
2005-07-13
2005-08-15
2005-05-24
and so forth...

I want to know if there´s a formula (of course there is! where I
can search for a value (date) in column B that is older then let say
2005-08-01 and find out how many dates are older than 2005-08-01 for
i.e Persson, Palle.

I hope I´ve made myself clear.

Ps. The date, i.e. 2005-08-01, is how me write it in Sweden.

 

[R.VENKATARAMAN]

the names are in A1 to A10 and dates are in B1 to B10(i have added dates)

now in an empty cell try this formula

=SUMPRODUCT(($B$1:$B$10<DATEVALUE("8/1/2005"))*($A$1:$A$10=A10))

a10 is having the name "Persson, Palle"


Hello!

This is what my worksheet looks like:

Column A (names of people):
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, Anna
Eriksson, AnnaPersson, Palle

Persson, Palle
Persson, Palle
Persson, Palle
Persson, Palle
and so forth...

Column B (dates):
2005-06-15
2005-07-13
2005-08-15
2005-05-24
and so forth...

I want to know if there´s a formula (of course there is! where I
can search for a value (date) in column B that is older then let say
2005-08-01 and find out how many dates are older than 2005-08-01 for
i.e Persson, Palle.

I hope I´ve made myself clear.

Ps. The date, i.e. 2005-08-01, is how me write it in Sweden.

 

[minismood]

Thanks you very much, bote of you!