• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run if formula on range of cells

J

jenwren

New Member
I am trying to run an if formula on a range of cells. I am trying to write a formula that would work like - IF(B4:B23="*ds*","","No DS")so if the letters ds are present in the selected range of cells it will return a blank or a 'No DS' accordingly, but the formula as I am attempting to write it only works on a single cell.


Any ideas please? Thank you.
 
Hi, jenwren!


Try this for case sensitivity:

=SI(ESERROR(ENCONTRAR("ds";B4:B23));"No DS";"") -----> in english: =IF(ISERROR(FIND("ds",B4:B23)),"No DS","")


And this for case insensitivity:

=SI(ESERROR(HALLAR("ds";B4:B23));"No DS";"") -----> in english: =IF(ISERROR(SEARCH("ds",B4:B23)),"No DS","")


Regards!
 
Adding to SirJB7's post, you'll need to confirm both of those formulas as an array by using Ctrl+Shift+Enter not just Enter.
 
Hi, jenwren!

Luke M's correction is right but I'm sorry to say that the formulas doesn't work in all cases, they just test the first cell in range, so please discard them for the time being. I'll try to update them and advise you, I apologize.

Regards!
 
Hi Jenwren!


Can you please try the below for me...


Code: 
=IF(SUMPRODUCT((ISNUMBER(FIND("DS",B4:B23))*1))>0,"DS Found Somewhere","No DS")


you'll need to confirm this formulas as an array by using Ctrl+Shift+Enter not just Enter.


You can use SEARCH also in case of case in-sensitive


Regards,

Deb


EDIT:

No Need to use Ctrl Shift Enter, as this part will be handle by SUMPRODUCT :)
 
@Debraj Roy

Hi!

I've tried with SUMPRODUCT, SUM and COUNTIF, combined with ISERROR, IFERROR, ... with no results yet :(

With SUMPRODUCT it doesn't work if no "ds" is present in range, it still displays Yes.

Regards!
 
Try this one (array)

Code: 
=IF(AND(ISERROR(SEARCH("ds",B4:B23))),"No DS","")


Reliazed that the problem is the ISERROR was generating an array of outputs, but the IF statement only has a single output. Need the Boolean check to look at all the ISERROR outputs and condense it into a single output.


@SirJB7

Debraj's formula is also working just fine for me. Problem with your data maybe?
 
@Luke

Hi!

Your formula works fine. But I still can't make Debraj Roy's one work, I'm trying with 5 values (a,b,c,d,e) and I get a blank. :(

Regards!


EDIT:


Both solutions work fine, just happened that Debraj Roy inverted the true/false labels and I didn't notice it and keep on testing my non-working case.


@Debraj Roy

Hi!

I'm wondering if you didn't it on purpose just to confuse me...

Regards!

PS: I'm sure you did it...
 
You must log in or register to reply here.
Back
Top