Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance - Vlookup
I could use some guidance on this formula:
{=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9) )*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0 )),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much info but my real problem is that the codes in n2:n31 are either a 0 or a 1. So I want the number of entries that will meet the first two parts with a 1 in the third area, and will want the number of entries that will meet the first two parts with a 0 in the third area. Currently my data should giveme 7 with a 1 in part three and 5 with a 0 in part three but instead I get a 12. I hope this is clear. Any assistance is appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance - Vlookup
Try this array formula
=SUM(IF(ISNUMBER(MATCH(E$8:E$55,Codes!$M$2:$M$31,0 )),IF((B8:B55="X")*(J8:J45={2,9})*(Codes!$N$2:$N$3 1=1),1))) -- HTH Bob "Bigfoot17" wrote in message ... I could use some guidance on this formula: {=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9) )*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0 )),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much info but my real problem is that the codes in n2:n31 are either a 0 or a 1. So I want the number of entries that will meet the first two parts with a 1 in the third area, and will want the number of entries that will meet the first two parts with a 0 in the third area. Currently my data should giveme 7 with a 1 in part three and 5 with a 0 in part three but instead I get a 12. I hope this is clear. Any assistance is appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance - Vlookup
When I enter as an array I get a n/a.
If not all rows are populated does that affect things? "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(MATCH(E$8:E$55,Codes!$M$2:$M$31,0 )),IF((B8:B55="X")*(J8:J45={2,9})*(Codes!$N$2:$N$3 1=1),1))) -- HTH Bob "Bigfoot17" wrote in message ... I could use some guidance on this formula: {=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9) )*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0 )),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much info but my real problem is that the codes in n2:n31 are either a 0 or a 1. So I want the number of entries that will meet the first two parts with a 1 in the third area, and will want the number of entries that will meet the first two parts with a 0 in the third area. Currently my data should giveme 7 with a 1 in part three and 5 with a 0 in part three but instead I get a 12. I hope this is clear. Any assistance is appreciated. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance - Vlookup
It shouldn't do, it didn't in my tests. Is there an #N/A in any of the
cells? -- HTH Bob "Bigfoot17" wrote in message ... When I enter as an array I get a n/a. If not all rows are populated does that affect things? "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(MATCH(E$8:E$55,Codes!$M$2:$M$31,0 )),IF((B8:B55="X")*(J8:J45={2,9})*(Codes!$N$2:$N$3 1=1),1))) -- HTH Bob "Bigfoot17" wrote in message ... I could use some guidance on this formula: {=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9) )*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0 )),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much info but my real problem is that the codes in n2:n31 are either a 0 or a 1. So I want the number of entries that will meet the first two parts with a 1 in the third area, and will want the number of entries that will meet the first two parts with a 0 in the third area. Currently my data should giveme 7 with a 1 in part three and 5 with a 0 in part three but instead I get a 12. I hope this is clear. Any assistance is appreciated. . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Assistance - Vlookup
No all cells have the proper content or are blank.
"Bob Phillips" wrote: It shouldn't do, it didn't in my tests. Is there an #N/A in any of the cells? -- HTH Bob "Bigfoot17" wrote in message ... When I enter as an array I get a n/a. If not all rows are populated does that affect things? "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(MATCH(E$8:E$55,Codes!$M$2:$M$31,0 )),IF((B8:B55="X")*(J8:J45={2,9})*(Codes!$N$2:$N$3 1=1),1))) -- HTH Bob "Bigfoot17" wrote in message ... I could use some guidance on this formula: {=SUM(IF((B$8:B$55="X")*(OR(J$8:J$55=2,J$8:J$55=9) )*(IF(VLOOKUP(E$8:E$55,Codes!$M$2:$N$31,2,0)=1,1,0 )),1,0))} I am trying to get a count of entries meeting three criteria. [1] If there is an "X" in column B [2] If the value in column J is a 2 or a 9 [3] If the value in column E is found in the vlookup(m2:m31) and the code(n2:31) is a '1' When I break it down and try running the parts, it seems to be fine. But as listed above the when parts [1] and [2] are true the entry is counted. This may be too much info but my real problem is that the codes in n2:n31 are either a 0 or a 1. So I want the number of entries that will meet the first two parts with a 1 in the third area, and will want the number of entries that will meet the first two parts with a 0 in the third area. Currently my data should giveme 7 with a 1 in part three and 5 with a 0 in part three but instead I get a 12. I hope this is clear. Any assistance is appreciated. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Assistance required | New Users to Excel | |||
Index and vlookup assistance | Excel Worksheet Functions | |||
Vlookup assistance | Excel Worksheet Functions | |||
Office Assistance: VLOOKUP, Example 3- Row headers 1-7 are wrong? | Excel Worksheet Functions | |||
IF & Vlookup Assistance | New Users to Excel |