IF(FIND Function with AND

Stephen Halsey

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

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")

Stephen Halsey

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

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))

Stephen Halsey

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

Happy to help. 👍️