| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANGE EXCEL copy cell that meets criteria in a range
on a data sheet all data is imputed - sheet 1
I have a range of cells ie a7 to a2028 with dates in them next range of cells b7 to b2028 has either the letter s or c in them next range of cells c7 to c2028 has numbers in them I want to have another sheet (sheet 2) that takes information from todays date and either s or c in column b ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take the value in column c that is in the same row as the criteria met above I tried using the below formula but get 0 for a response =IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser Data'!C7:C2120,0) Please help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANGE EXCEL copy cell that meets criteria in a range
If the values in column C are numeric values, and there will only be one
match, then you could use a SUMPRODUCT. =SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser Data'!B7:B2028="s"),'Ser Data'!C7:C2028) or if there will be multiple matches and you want to sum the numeric values in column that meet the criteria, then the above formula will work for that as well. Else, I would suspect a Pivot Table might be helpful. HTH, Paul -- "confused" wrote in message ... on a data sheet all data is imputed - sheet 1 I have a range of cells ie a7 to a2028 with dates in them next range of cells b7 to b2028 has either the letter s or c in them next range of cells c7 to c2028 has numbers in them I want to have another sheet (sheet 2) that takes information from todays date and either s or c in column b ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take the value in column c that is in the same row as the criteria met above I tried using the below formula but get 0 for a response =IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser Data'!C7:C2120,0) Please help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANGE EXCEL copy cell that meets criteria in a range
That formula works perfectly. Thank you very much
Confused "PCLIVE" wrote: If the values in column C are numeric values, and there will only be one match, then you could use a SUMPRODUCT. =SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser Data'!B7:B2028="s"),'Ser Data'!C7:C2028) or if there will be multiple matches and you want to sum the numeric values in column that meet the criteria, then the above formula will work for that as well. Else, I would suspect a Pivot Table might be helpful. HTH, Paul -- "confused" wrote in message ... on a data sheet all data is imputed - sheet 1 I have a range of cells ie a7 to a2028 with dates in them next range of cells b7 to b2028 has either the letter s or c in them next range of cells c7 to c2028 has numbers in them I want to have another sheet (sheet 2) that takes information from todays date and either s or c in column b ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take the value in column c that is in the same row as the criteria met above I tried using the below formula but get 0 for a response =IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser Data'!C7:C2120,0) Please help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANGE EXCEL copy cell that meets criteria in a range
You're welcome.
-- "confused" wrote in message ... That formula works perfectly. Thank you very much Confused "PCLIVE" wrote: If the values in column C are numeric values, and there will only be one match, then you could use a SUMPRODUCT. =SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser Data'!B7:B2028="s"),'Ser Data'!C7:C2028) or if there will be multiple matches and you want to sum the numeric values in column that meet the criteria, then the above formula will work for that as well. Else, I would suspect a Pivot Table might be helpful. HTH, Paul -- "confused" wrote in message ... on a data sheet all data is imputed - sheet 1 I have a range of cells ie a7 to a2028 with dates in them next range of cells b7 to b2028 has either the letter s or c in them next range of cells c7 to c2028 has numbers in them I want to have another sheet (sheet 2) that takes information from todays date and either s or c in column b ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take the value in column c that is in the same row as the criteria met above I tried using the below formula but get 0 for a response =IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser Data'!C7:C2120,0) Please help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy range and paste into every 3rd cell of new range | New Users to Excel | |||
| Copy row details where cell value meets certain criteria | Excel Worksheet Functions | |||
| copy row to new sheet where cell value meets criteria | Excel Discussion (Misc queries) | |||
| Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
| Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Linear Mode
