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, Inflight, 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

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

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👍️

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!