| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching full name to 'two column' name using sumproduct
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
Try something like this...
A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
Hi Biff,
Looks as if it might work however I have just realised that for this particular sheet, instead of adding a value in column F I need to count the number of rows in sheet 1 that are in that particular month. Do I still use SUMPRODUCT or should I in fact be using something else. sorry -- any help gratefully received thanks carrach "T. Valko" wrote: Try something like this... A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
For a count just drop the last array (the sum array Sheet1!F2:F100):
=SUMPRODUCT(--(Sheet1!A2:A100&" "&Sheet1!B2:B100=A2),--(MONTH(Sheet1!D2:D100)=4)) Whe A2 = Fred Smith Sheet1!A2:A100 = first names Sheet1!B2:B100 = last names -- Biff Microsoft Excel MVP "Carrach" wrote in message ... Hi Biff, Looks as if it might work however I have just realised that for this particular sheet, instead of adding a value in column F I need to count the number of rows in sheet 1 that are in that particular month. Do I still use SUMPRODUCT or should I in fact be using something else. sorry -- any help gratefully received thanks carrach "T. Valko" wrote: Try something like this... A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
YES YES YES - Thank you so much for all your help it all works beautifully.
-- thanks carrach "T. Valko" wrote: For a count just drop the last array (the sum array Sheet1!F2:F100): =SUMPRODUCT(--(Sheet1!A2:A100&" "&Sheet1!B2:B100=A2),--(MONTH(Sheet1!D2:D100)=4)) Whe A2 = Fred Smith Sheet1!A2:A100 = first names Sheet1!B2:B100 = last names -- Biff Microsoft Excel MVP "Carrach" wrote in message ... Hi Biff, Looks as if it might work however I have just realised that for this particular sheet, instead of adding a value in column F I need to count the number of rows in sheet 1 that are in that particular month. Do I still use SUMPRODUCT or should I in fact be using something else. sorry -- any help gratefully received thanks carrach "T. Valko" wrote: Try something like this... A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Carrach" wrote in message ... YES YES YES - Thank you so much for all your help it all works beautifully. -- thanks carrach "T. Valko" wrote: For a count just drop the last array (the sum array Sheet1!F2:F100): =SUMPRODUCT(--(Sheet1!A2:A100&" "&Sheet1!B2:B100=A2),--(MONTH(Sheet1!D2:D100)=4)) Whe A2 = Fred Smith Sheet1!A2:A100 = first names Sheet1!B2:B100 = last names -- Biff Microsoft Excel MVP "Carrach" wrote in message ... Hi Biff, Looks as if it might work however I have just realised that for this particular sheet, instead of adding a value in column F I need to count the number of rows in sheet 1 that are in that particular month. Do I still use SUMPRODUCT or should I in fact be using something else. sorry -- any help gratefully received thanks carrach "T. Valko" wrote: Try something like this... A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
Hi,
use sumproduct in my first post the formula has the month3 =SUMPRODUCT(--(sa_advisor_LAST_NAME=c1),--(sa_advisor_FIRST_NAME=c2),--(MONTH(Sheet1!D2:D100)=4),Sheet1!F2:F100) "Carrach" wrote: Hi Biff, Looks as if it might work however I have just realised that for this particular sheet, instead of adding a value in column F I need to count the number of rows in sheet 1 that are in that particular month. Do I still use SUMPRODUCT or should I in fact be using something else. sorry -- any help gratefully received thanks carrach "T. Valko" wrote: Try something like this... A2 = Fred Smith column C = first names column D = last names =SUMPRODUCT(--(C2:C5&" "&D2:D5=A2)..... -- Biff Microsoft Excel MVP "Carrach" wrote in message ... 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 . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching full name to 'two column' name using sumproduct
Hi,
if you have for example Fred in cell C1 and Smith in C2 change formula as follow =SUMPRODUCT(--(sa_advisor_LAST_NAME=c2),--(sa_advisor_FIRST_NAME=c1),--(MONTH(Sheet1!D2:D100)=4),Sheet1!F2:F100) "Carrach" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| add data in column related to cell in another column - see full de | Excel Worksheet Functions | |||
| sumproduct with partial charcter matching | Excel Worksheet Functions | |||
| Copy Filtered column in Full | Excel Discussion (Misc queries) | |||
| How I can print full text bigger than column, in repeat column | Excel Discussion (Misc queries) | |||
| How to specify almost full-column arguments like A3:A | Excel Discussion (Misc queries) | |||
Hybrid Mode
