IF(FIND Function with AND

Hi Everyone,

I have created a formula which works well with locating and counting text in a range - "COUNTIF([Functional Area]:[Functional Area], FIND("Accounting",@cell)>0)". This formula counts the total number of items irrespective of the status of the item, which could be for example, New Demand, In-flight, UAT, Complete, etc. As such i need to run the formula above and include the AND statement. For example ""COUNTIF([Functional Area]:[Functional Area], FIND("Accounting",@cell)>0) AND (Status:Status, " New Demand").

Any help creating this formula would be appreciated.

Thanks,

Steve

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To evaluate multiple range/criteria sets you would need to use a OCUNTIFS function (with the S on the end).

    =COUNTIFS([Functional Area]:[Functional Area], FIND("Accounting",@cell) > 0, Status:Status, "New Demand")

  • Hi Paul,

    Thanks for the feedback; however, I'm still having a challenge with making the formula work and obtain the #UNPARSEABLE error message.

    The full formula I have used is as follows, which references another sheet i.e.:

    1) Consolidated Report Inventory Range 1 (which refers to the 'Functional Area' e.g. "Accounting")

    2) Consolidated Report Inventory Range 2 (which refer to the Status e.g. "0) New Demand")

    =COUNTIFS({Consolidated Report Inventory Range 1}, FIND("Accounting",cell) >0, {Consolidated Report Inventory Range 2}, "0) New Demand")

    Thanks in advance,

    Stephen


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It looks like it isn't liking that closing parenthesis in the last criteria. Try this for the criteria instead:

    =COUNTIFS(.........................................................., {Range 2}, CONTAINS("New Demand", @cell))

  • Thanks Paul. That works! per the formula below.

    =COUNTIFS({Consolidated Report Inventory Range 1}, FIND("Accounting", @cell) > 0, {Consolidated Report Inventory Range 2}, CONTAINS("0) New Demand", @cell))

    I may have a couple of further questions during the course of the week.

    Cheers,

    Steve👍️

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!