View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
carrach carrach is offline
external usenet poster
 
Posts: 25
Default matching full name to 'two column' name using sumproduct

Hi Eduardo,
many thanks, cant quite get it to work for me.
Is there any way of checking the names in colA and colB together in sheet1
to see if together they match cell A2 in sheet2? without having to type each
name into the formulae individually as I have about a hundred to do. sheet 1
and sheet 2 are in different workbooks. I am using excel 2003.
what I need is:
in cell E5 sheet2:
if first name1 and surname1 in sheet 1 match full name in cell A2 sheet 2,
AND the month =4 in column D sheet 1, then add the values in column F sheet 1,
Hope that makes sense

thanks
carrach


"Eduardo" wrote:

Hi,

=SUMPRODUCT(--(sa_advisor_LAST_NAME="Smith"),--(sa_advisor_FIRST_NAME="Fred"),--(MONTH(Sheet1!D2:D100)=4),Sheet1!
F2:F100)
)


"Carrach" wrote:

Assume your names in Sheet1 are in column A, the dates are in column
D, and the values you want to add are in column F. Further assume that
the target_name in this_sheet is in A2. Try this formula in a cell in
this_sheet:
=SUMPRODUCT(--(Sheet1!A2:A100=A2),--(MONTH(Sheet1!D2:D100)=4),Sheet1!
F2:F100)
This formulae works very well (thanks to Pete for his help), however I need
to use the same formulae to match the name in A2 to a spreadsheet that has
the name to be matched to in two columns (first name (col A), last name (Col
B).
I currently use the following to match names in this manner but dont know
how to use it in the formulae above:
=SUMPRODUCT(--(sa_advisor_LAST_NAME="Smith"),--(sa_advisor_FIRST_NAME="Fred"))
Can anyone help please?
regards
Carrach