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. |
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 |