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
-
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!