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" skrev i en
meddelelse
...
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" skrev i en meddelelse
...
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.