| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match multiple criteria ina range from multiple criteria multiplet
This is a tough one i think
been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multiplet
mistyped on of the columns
in the MON column F is a range of times and in sheet 1 F. is range of time out -- RG "RG" wrote: This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multiplet
Try it like this:
=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A3:A6500,Mon!E7:E100,0))),--(ISNUMBER(MATCH(Sheet1!D3:D6500,Mon!K7:K100,0))),--(Sheet1!E3:E6500=Mon!F7:F100),--(Sheet1!F3:F6500<=Mon!F7:F100),--(Sheet1!B3:B6500=Report!B7)) -- Biff Microsoft Excel MVP "RG" wrote in message ... This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multi
tried. still getting #N/A. not sure if this will help but Not all cells
throughout the ranges have information in them. alot of them are blank - RG "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A3:A6500,Mon!E7:E100,0))),--(ISNUMBER(MATCH(Sheet1!D3:D6500,Mon!K7:K100,0))),--(Sheet1!E3:E6500=Mon!F7:F100),--(Sheet1!F3:F6500<=Mon!F7:F100),--(Sheet1!B3:B6500=Report!B7)) -- Biff Microsoft Excel MVP "RG" wrote in message ... This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multi
Do you have any #N/A errors in any of your ranges?
In your post you had these expressions backwards: (Sheet1!E3:E6500=Mon!F7:F100) (Sheet1!F3:F6500<=Mon!F7:F100) You had: --(Mon!F7:F100=Sheet1!E3:E6500) --(Mon!F7:F100<=Sheet1!F3:F6500) Thoses expressions will cause a #N/A error because the ranges being tested are not the same size as the ranges in the other expressions. -- Biff Microsoft Excel MVP "RG" wrote in message ... tried. still getting #N/A. not sure if this will help but Not all cells throughout the ranges have information in them. alot of them are blank - RG "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A3:A6500,Mon!E7:E100,0))),--(ISNUMBER(MATCH(Sheet1!D3:D6500,Mon!K7:K100,0))),--(Sheet1!E3:E6500=Mon!F7:F100),--(Sheet1!F3:F6500<=Mon!F7:F100),--(Sheet1!B3:B6500=Report!B7)) -- Biff Microsoft Excel MVP "RG" wrote in message ... This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multi
Argh!
Disregard. Don't know what I was thinking (apparently I wasn't!). -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Do you have any #N/A errors in any of your ranges? In your post you had these expressions backwards: (Sheet1!E3:E6500=Mon!F7:F100) (Sheet1!F3:F6500<=Mon!F7:F100) You had: --(Mon!F7:F100=Sheet1!E3:E6500) --(Mon!F7:F100<=Sheet1!F3:F6500) Thoses expressions will cause a #N/A error because the ranges being tested are not the same size as the ranges in the other expressions. -- Biff Microsoft Excel MVP "RG" wrote in message ... tried. still getting #N/A. not sure if this will help but Not all cells throughout the ranges have information in them. alot of them are blank - RG "T. Valko" wrote: Try it like this: =SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A3:A6500,Mon!E7:E100,0))),--(ISNUMBER(MATCH(Sheet1!D3:D6500,Mon!K7:K100,0))),--(Sheet1!E3:E6500=Mon!F7:F100),--(Sheet1!F3:F6500<=Mon!F7:F100),--(Sheet1!B3:B6500=Report!B7)) -- Biff Microsoft Excel MVP "RG" wrote in message ... This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multiplet
Well after you braught it up i did find a mistake
Mon!F7:F51=Sheet1!E3:E8 Mon!F7:F51<=Sheet1!F3:F8 the referances should be like this -- RG "RG" wrote: This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multi
Bah now im getting confused it ws like that already.
-- RG "RG" wrote: Well after you braught it up i did find a mistake Mon!F7:F51=Sheet1!E3:E100 Mon!F7:F51<=Sheet1!F3:F100 the referances should be like this -- RG "RG" wrote: This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match multiple criteria ina range from multiple criteria multi
Disregard this. i reworked a few things and figured it out
-- RG "RG" wrote: Bah now im getting confused it ws like that already. -- RG "RG" wrote: Well after you braught it up i did find a mistake Mon!F7:F51=Sheet1!E3:E100 Mon!F7:F51<=Sheet1!F3:F100 the referances should be like this -- RG "RG" wrote: This is a tough one i think been trying to use sum product but have not been able to get it to work =SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D3:D6500=Mon!K7:K100),--(Mon!F7:F100=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7)) this is basically matching up criteria that is in one range of cells with criteria in the other ranges of cells in multiple instances. if all five conditions are met, it would equal 1 Sheet1 columns A,B,D,E,F A.Range of dates, B. range of Names, D. range of random numbers, E. range oftime in, F. range of time Mon Columns E,F,K E. range of dates, F. Range of dates K.range of random numbers Report name here needs to be found in sheet 1 in the range of names if everything matches at least once it should equal 1. if it matches more then obviously it would equal 2 -- RG |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| Index/Match Multiple Criteria | Excel Discussion (Misc queries) | |||
| To Biff: Using Match with multiple criteria | Excel Worksheet Functions | |||
| Using Match with multiple criteria | Excel Worksheet Functions | |||
Hybrid Mode
