Formula Assistance - Vlookup

[Bigfoot17]

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.

 

[Bob Phillips]

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$31=1),1)))

 

[Bigfoot17]

When I enter as an array I get a n/a.

If not all rows are populated does that affect things?

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$31=1),1)))

--

HTH

Bob

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.

.

 

[Bob Phillips]

It shouldn't do, it didn't in my tests. Is there an #N/A in any of the
cells?

--

HTH

Bob

When I enter as an array I get a n/a.

If not all rows are populated does that affect things?

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$31=1),1)))

--

HTH

Bob

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.

.

 

[Bigfoot17]

No all cells have the proper content or are blank.

It shouldn't do, it didn't in my tests. Is there an #N/A in any of the
cells?

--

HTH

Bob

When I enter as an array I get a n/a.

If not all rows are populated does that affect things?

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$31=1),1)))

--

HTH

Bob

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.


.

.