IF, AND, OR Formulas
Using my data set, I would like to know by region the number of multi-year reporters and new reporters (2018). I will count either status as an active reporters.
Ex. Africa= total 2 active reporters
-Ghana: Multi-Year Reporter
-South Africa: New-Reporter
-Nigeria: Did not report
The following formula will count the number of multi-year reports, but I cannot seem to add a statement to make it include both multi-year and new reporters.
=COUNTIFS({Data_Regional GRF LUW Report Tracker Range 1}, Regions1, {Data_Regional GRF LUW Report Tracker Range 2}, "Multi-Year Reporter")
I tried to create this:
=COUNTIFS({Data_Regional GRF LUW Report Tracker Range 1}, Regions1, AND{Data_Regional GRF LUW Report Tracker Range 2}, "Multi-Year Reporter", OR{Data_Regional GRF LUW Report Tracker Range 2,}"New Reporter")
Can anyone tell me how to correct my formula?
Thanks!
Comments
-
The COUNTIFS is looking for this type of syntax.
=COUNTIFS(criteria range 1, criteria 1, criteria range 2, criteria 2)
NOTE: The AND function is already implied within the COUNTIFS. You do not need to specify this and doing so incorrectly will break your formula every time.
The AND and OR functions have a different syntax all together.
AND(logical statement 1, logical statement 2)
OR(logical statement 1, logical statement 2)
Using the COUNTIFS syntax within the OR function is where your problem lies once we remove the already implied AND function. You have to replace it with logical statements. To do this, you would use @cell references and only specify the criteria for the range that was already established in the COUNTIFS.
=COUNTIFS(range 1, criteria 1, range 2, OR(@cell = "this", @cell = "that"))
=COUNTIFS({Data_Regional GRF LUW Report Tracker Range 1}, Regions1, {Data_Regional GRF LUW Report Tracker Range 2}, OR(@cell = "Multi-Year Reporter", @cell = "New Reporter"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!