Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match multiple criteria in both rows and columns
I have looked on these threads for a while now and have come up with 2
different formulas as a result to get what I need. However, both formulas give me the #NUM! error. I have a number of timesheets that are broken out by area, by quarter, and by day. I have a summary spreadsheet that I am trying to sum all of the hours by week and by each area and each quarter. Following are the 2 formulas I have constructed to serve this purpose. Can anyone tell me what I am doing wrong? =SUMPRODUCT((Booth!A:A='NU Schedule'!A1)*(Booth!B:B=1)*(Booth!D:D="Total")*(B ooth!2:2='NU Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000)) =INDEX(Booth!$1:$65536,MATCH(1,(Booth!A:A='NU Schedule'!$A$1)*(Booth!B:B=1)*(Booth!D:D="Total"), 0),17) I have already tried pressing ctrl+shift+enter for the array, but this has not worked. Also, when I put these formulas into one cell, all of the other cells with formulas pulling data from that worksheet that worked previously all of a sudden say #N/A. Can someone tell me why that is also? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match multiple criteria in both rows and columns
Hi
=SUMPRODUCT((Booth!A:A='NU Schedule'!A1)*(Booth!B:B=1)*(Booth!D:D="Total")*(B ooth!2:2='NU Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000)) 1. Can't use B:B references in 2003 or earlier 2. You range reference must all be the same length - you can't multiply B:B*I3:IV65000 they don't have the same number of rows. There may be other problems, but this should get you started. -- If this helps, please click the Yes button Cheers, Shane Devenshire "jbo" wrote: I have looked on these threads for a while now and have come up with 2 different formulas as a result to get what I need. However, both formulas give me the #NUM! error. I have a number of timesheets that are broken out by area, by quarter, and by day. I have a summary spreadsheet that I am trying to sum all of the hours by week and by each area and each quarter. Following are the 2 formulas I have constructed to serve this purpose. Can anyone tell me what I am doing wrong? =SUMPRODUCT((Booth!A:A='NU Schedule'!A1)*(Booth!B:B=1)*(Booth!D:D="Total")*(B ooth!2:2='NU Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000)) =INDEX(Booth!$1:$65536,MATCH(1,(Booth!A:A='NU Schedule'!$A$1)*(Booth!B:B=1)*(Booth!D:D="Total"), 0),17) I have already tried pressing ctrl+shift+enter for the array, but this has not worked. Also, when I put these formulas into one cell, all of the other cells with formulas pulling data from that worksheet that worked previously all of a sudden say #N/A. Can someone tell me why that is also? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match multiple criteria in both rows and columns
That is very helpful. The information about B:B references will help me with
the other formula. Thanks! "Shane Devenshire" wrote: Hi =SUMPRODUCT((Booth!A:A='NU Schedule'!A1)*(Booth!B:B=1)*(Booth!D:D="Total")*(B ooth!2:2='NU Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000)) 1. Can't use B:B references in 2003 or earlier 2. You range reference must all be the same length - you can't multiply B:B*I3:IV65000 they don't have the same number of rows. There may be other problems, but this should get you started. -- If this helps, please click the Yes button Cheers, Shane Devenshire "jbo" wrote: I have looked on these threads for a while now and have come up with 2 different formulas as a result to get what I need. However, both formulas give me the #NUM! error. I have a number of timesheets that are broken out by area, by quarter, and by day. I have a summary spreadsheet that I am trying to sum all of the hours by week and by each area and each quarter. Following are the 2 formulas I have constructed to serve this purpose. Can anyone tell me what I am doing wrong? =SUMPRODUCT((Booth!A:A='NU Schedule'!A1)*(Booth!B:B=1)*(Booth!D:D="Total")*(B ooth!2:2='NU Schedule'!C68)*(Booth!2:2<'NU Schedule'!C69)*(Booth!I3:IV65000)) =INDEX(Booth!$1:$65536,MATCH(1,(Booth!A:A='NU Schedule'!$A$1)*(Booth!B:B=1)*(Booth!D:D="Total"), 0),17) I have already tried pressing ctrl+shift+enter for the array, but this has not worked. Also, when I put these formulas into one cell, all of the other cells with formulas pulling data from that worksheet that worked previously all of a sudden say #N/A. Can someone tell me why that is also? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Searching rows with multiple columns criteria | Excel Worksheet Functions | |||
How can I retrieve multiple rows that match one criteria, i.e. a d | Excel Worksheet Functions |