IF with multiple options alphanumeric
 

Hello everyone,

Hoping someone can help me with this one - it has me stumped.

I have a spreadsheet that has an overall category in row 3, with
subcategories under in rows 4 through 10

In cells B4:B10 are alpha values: "Level 1", "Level 2", or "Level 1&2"

Here is what I would like to appear in cell B3:
If "Level 1&2" appears anywhere in B4:B10, returns "Level 1&2"
If "Level 1" and "Level 2" appear over the range B4:B10, returns "Level 1&2"
If only "Level 1" appears anywhere over range B4:B10, returns "Level 1"
If only "Level 2" appears anywhere over range, returns "Level 2"

Does anyone know a formula that will work for this?
Thanks

IF with multiple options alphanumeric
 

How about this?

=IF(COUNTIF(B4:B10,"Level 1")=ROWS(B4:B10),"Level
1",IF(COUNTIF(B4:B10,"Level 2")=ROWS(B4:B10),"Level 2","Level 1&2"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cisgalk" wrote:

Hello everyone,

Hoping someone can help me with this one - it has me stumped.

I have a spreadsheet that has an overall category in row 3, with
subcategories under in rows 4 through 10

In cells B4:B10 are alpha values: "Level 1", "Level 2", or "Level 1&2"

Here is what I would like to appear in cell B3:
If "Level 1&2" appears anywhere in B4:B10, returns "Level 1&2"
If "Level 1" and "Level 2" appear over the range B4:B10, returns "Level 1&2"
If only "Level 1" appears anywhere over range B4:B10, returns "Level 1"
If only "Level 2" appears anywhere over range, returns "Level 2"

Does anyone know a formula that will work for this?
Thanks

IF with multiple options alphanumeric
 

Try
=IF(AND(COUNTIF(B4:B10,"*1*"),COUNTIF(B4:B10,"*2*" )),"Level 1 &
2",IF(COUNTIF(B4:B10,"*1*"),"Level 1",IF(COUNTIF(B4:B10,"*2*"),"Level 2","")))

If this post helps click Yes
---------------
Jacob Skaria


"cisgalk" wrote:

Hello everyone,

Hoping someone can help me with this one - it has me stumped.

I have a spreadsheet that has an overall category in row 3, with
subcategories under in rows 4 through 10

In cells B4:B10 are alpha values: "Level 1", "Level 2", or "Level 1&2"

Here is what I would like to appear in cell B3:
If "Level 1&2" appears anywhere in B4:B10, returns "Level 1&2"
If "Level 1" and "Level 2" appear over the range B4:B10, returns "Level 1&2"
If only "Level 1" appears anywhere over range B4:B10, returns "Level 1"
If only "Level 2" appears anywhere over range, returns "Level 2"

Does anyone know a formula that will work for this?
Thanks

IF with multiple options alphanumeric
 

Jacob

Perfect, works perfectly, thanks so much. I didn't think about using
wildcards.



"Jacob Skaria" wrote:

Try
=IF(AND(COUNTIF(B4:B10,"*1*"),COUNTIF(B4:B10,"*2*" )),"Level 1 &
2",IF(COUNTIF(B4:B10,"*1*"),"Level 1",IF(COUNTIF(B4:B10,"*2*"),"Level 2","")))

If this post helps click Yes
---------------
Jacob Skaria


"cisgalk" wrote:

Hello everyone,

Hoping someone can help me with this one - it has me stumped.

I have a spreadsheet that has an overall category in row 3, with
subcategories under in rows 4 through 10

In cells B4:B10 are alpha values: "Level 1", "Level 2", or "Level 1&2"

Here is what I would like to appear in cell B3:
If "Level 1&2" appears anywhere in B4:B10, returns "Level 1&2"
If "Level 1" and "Level 2" appear over the range B4:B10, returns "Level 1&2"
If only "Level 1" appears anywhere over range B4:B10, returns "Level 1"
If only "Level 2" appears anywhere over range, returns "Level 2"

Does anyone know a formula that will work for this?
Thanks

IF with multiple options alphanumeric
 

I probably should have specified that there may be no data also. Thanks
though - did get the solution.

"Luke M" wrote:

How about this?

=IF(COUNTIF(B4:B10,"Level 1")=ROWS(B4:B10),"Level
1",IF(COUNTIF(B4:B10,"Level 2")=ROWS(B4:B10),"Level 2","Level 1&2"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cisgalk" wrote:

Hello everyone,

Hoping someone can help me with this one - it has me stumped.

I have a spreadsheet that has an overall category in row 3, with
subcategories under in rows 4 through 10

In cells B4:B10 are alpha values: "Level 1", "Level 2", or "Level 1&2"

Here is what I would like to appear in cell B3:
If "Level 1&2" appears anywhere in B4:B10, returns "Level 1&2"
If "Level 1" and "Level 2" appear over the range B4:B10, returns "Level 1&2"
If only "Level 1" appears anywhere over range B4:B10, returns "Level 1"
If only "Level 2" appears anywhere over range, returns "Level 2"

Does anyone know a formula that will work for this?
Thanks