Using COUNTIFS with OR function spread across multiple ranges

Options
edited 03/15/22

Hello,

I am trying to build a COUNTIF function that will count all cells which meet criteria 1 and either criteria 2 or 3, which are found in different ranges.

Example: In Sheet1, I have the following columns: shape, color1, and color2. In Sheet 2, I would like to create a function which counts all observations in Sheet1 where shape=square, and either color1=blue OR color2=blue.

If applied to the data set below, the function in Sheet2 should correctly return '2' :

Sheet1

shape color1 color2

square blue green

square red red

circle blue red

square red blue

Tags:

• ✭✭✭✭✭✭
Options

You are going to need two separate COUNTIFS and add them together.

=COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 1 Column}, @cell = "Blue") + COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 2 Column}, @cell = "Blue")

• ✭✭✭
Options

you can use this

=COUNTIF(([Shape]@row), AND(([Shape]@row = "square"), (OR(([color1]@row = "blue"), ([color2]@row = "blue")))))

as a column function, on a new column for instance, [new colum].

use : =countif([new colum]:[new colum],1) to get total count

• Options

Hi,

Thanks for your response, but this returned an UNPARSEABLE error. I forgot to mention that the color1 and color2 columns are actually multi-select and thus contain multiple responses, so I think the CONTAINS function should also be nested inside the formula. The adjusted example would be:

Sheet1

shape...................color1.....................color2

square..................blue, green.............green, red

square..................red, green...............red, red

circle....................blue, red, black.......green

square..................red, blue..................blue, green

Here's what I have so far, per the example:

=COUNTIF(([shape]@row), AND(([shape]@row = "square"), (OR(CONTAINS("blue", [color1])), (CONTAINS("blue", [color2])))))

• ✭✭✭✭✭✭
Options

You are going to need two separate COUNTIFS and add them together.

=COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 1 Column}, @cell = "Blue") + COUNTIFS({Sheet 1 Shape Column}, @cell = "Square", {Sheet 1 Color 2 Column}, @cell = "Blue")

• Options

That worked! Thanks, Paul.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• edited 12/26/23
Options

I have something similar I need help with.. I need to create a sheet summary formula that will count IFs the first qualifier is the [Project started]:[Project started] column contains a check mark. The second qualifier is if the [Status]:[Status] column contains either "06 - Project Engaged" or "07 - On Hold (See Notes)".

What I have now is not working.

=COUNTIFS([Project started]:[Project started], 1, OR([Status]:[Status], "06 - Project Engaged", [Status]:[Status], "07 - On Hold (See Notes)"))

Disregard, I figured it out.. =COUNTIFS([Project started]:[Project started], 1, Status:Status, "06 - Project Engaged") + COUNTIFS([Project started]:[Project started], 1, Status:Status, "07 - On Hold (See Notes)")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!