search value based on 2 criteria

[Guest]

Hi all

I currently have an excel spreadsheet with 3 columns. Namely, Date, Name and Amount. In another summary worksheet, the total amount paid by each person every month and the date of payment will be displayed. I've managed to sum the total amt paid per person with help of Norman Harker in this forum. But I am still stuck at how to retrieve the payment dates in my summary? I tried Vlookup but unsuccessful as I need to search the data based on 2 criterias Name and Month. Is there anyway to do this using excel worksheet functions

Please refer to the following sample data for an example of what I am trying to do

Date Name Amoun
12-Jan-04 Tanya 480.5
14-Jan-04 Simon 500.0
24-Jan-04 Tanya 16.2
1-Feb-04 Clement 452.5
10-Feb-04 Tanya 12.5
....
The results I would like to get in Summary is as follo
Month Name Total Amount Date receive
January Clement 0.00
January Simon 500.00 14-Jan-0
January Tanya 496.75 12-Jan-04, 24-Jan-0
February Clement 452.55 1-Feb-0
February Simon 0.00
February Tanya 12.54 10-Feb-0

Thanks
yann

 

[Frank Kabel]

Hi
not possible with worksheet functions alone as you want to return
multiple values for the date received. This would require VBA (e.g. a
user defined function). So is VBA a solution for you?

 

[Guest]

Hi Frank

Thanks for your reply.

Say if I only need to return one value for the date received, isit possible to retrieve the values using worksheet function alone

thanks
yann

 

[Frank Kabel]

Hi
to return for example the last date in a month use
=SUMPRODUCT(MAX((MONTH(A1:A100)=1)*(YEAR(A1:A100)=2004)*
(B1:B100="Tanya")*(A1:A100)))

and format this cell as date

-----Original Message-----
Hi Frank,

Thanks for your reply.

Say if I only need to return one value for the date

received, isit possible to retrieve the values using
worksheet function alone?

 

[Frank Kabel]

Hi
and how do you identify the last payment date for a month?

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

Thanks for the help, but the formula you provided seems to be

retrieving the last date for that student instead of the last payment
date of that student for that January.

 

[Guest]

Hi Frank

Actually I am still trying to modify the formula u sent me to see if I can get the last date each student made payment for each month.. Still unsuccessful

Do help me if you have any idea how I can do that.

Thank
yann

 

[Frank Kabel]

Hi
if you want email me your file and I'll have a look at it. The formula
should work (will give you the last date for January for the student
'Tanya')

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

Actually I am still trying to modify the formula u sent me to see if

I can get the last date each student made payment for each month..
Still unsuccessful.