Combining LOOKUP and COUNTIF functions

[Guest]

I am trying to combine the LOOKUP and COUNTIF functions--because I cannot
nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)
an
=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first
function is zero, but return a 3 if the first function is greater than
zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!
Kate

 

[Hans Knudsen]

If I understand you correct.
Let's say you have your COUNTIF formula i cell G4 then try:
=IF(G4=0,0,INDEX({3,3,3,3,3,2,2,1,0,1,3},MATCH(G2,{"P","S","B","A","E","T","XX","BB","BBB","TB","TBB"},0)))

Note that I have entered XX instead of your B as you have B two
times and want 3 returned in the first case and 2 in the second
case. Is that really what you want?

Hans


"kate_suzanne" <[email protected]> skrev i en
meddelelse
news:[email protected]...

 

[Hans Knudsen]

If I understand you correct.
Let's say you have your COUNTIF formula i cell G4 then try:
=IF(G4=0,0,INDEX({3,3,3,3,3,2,2,1,0,1,3},MATCH(G2,{"P","S","B","A","E","T","XX","BB","BBB","TB","TBB"},0)))

Note that I have entered XX instead of your B as you have B two
times and want 3 returned in the first case and 2 in the second
case. Is that really what you want?

Hans


"kate_suzanne" <[email protected]> skrev i en
meddelelse
news:[email protected]...

 

[Leo Heuser]

I am trying to combine the LOOKUP and COUNTIF functions--because I cannot
nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)
and
=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first
function is zero, but return a 3 if the first function is greater than
zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!
Kate

Kate

BTW "B" is twice in the array.

Is this, what you are looking for?

=IF(SUM(COUNTIF(G2:M2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB"})),3,0)

or maybe this one:

=IF(SUMPRODUCT(ISERROR(MATCH(G2:M2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB"},0))+0)=0,3,0)

 

[Leo Heuser]

I am trying to combine the LOOKUP and COUNTIF functions--because I cannot
nest more than 7 functions.

Here are the two functions I have:

=IF(1-COUNTIF(G2:M2,"P")>0,1-COUNTIF(G2:M2,"P"),0)
and
=LOOKUP(G2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB",""},{3,3,3,3,3,2,2,1,0,1,0,3})

How can I combine these two functions to return a zero value if the first
function is zero, but return a 3 if the first function is greater than
zero????

I can e-mail my file if that helps find a solution?!?!?!?

Thanks!
Kate

Kate

BTW "B" is twice in the array.

Is this, what you are looking for?

=IF(SUM(COUNTIF(G2:M2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB"})),3,0)

or maybe this one:

=IF(SUMPRODUCT(ISERROR(MATCH(G2:M2,{"P","S","B","A","E","T","B","BB","BBB","TB","TBB"},0))+0)=0,3,0)