Combining LOOKUP and COUNTIF functions

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

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" <katesuzanne@discussions.microsoft.com> skrev i en
meddelelse
news:7EDE22D6-E13E-4269-BF4C-FB12709108D6@microsoft.com...
>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_suzanne" <katesuzanne@discussions.microsoft.com> skrev i en meddelelse
news:7EDE22D6-E13E-4269-BF4C-FB12709108D6@microsoft.com...
>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)


--
Best regards
Leo Heuser

Followup to newsgroup only please.