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

  • Thread starter Thread starter minismood
  • Start date Start date
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.
 
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.
 
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.
 
Back
Top