IF, AND, OR Formulas

Jasmine Garcia · 09/11/19

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!

Paul Newcome · 09/12/19

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

IF, AND, OR Formulas

Comments

Help Article Resources

Categories