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

M

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.
 
R

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.
 
Top