Countifs formula with and/or criteria across multiple columns

J. Earp

I need to set up a COUNTIFs formula that counts data if Column 1 contains a red circle and Column 2 or 3 or 4 contains a value. How do I differentiate the three columns that have the OR option in my formula?  Thank you in advance for your help/guidance!

Paul Newcome

You would need a helper column that has an IF/OR statement in it to indicate when one of those three columns has the value. Then you would use a COUNTIFS to evaluate the helper column and column 1.

J. Earp

Thanks Paul! So if I have six different potential values that I would want to count in columns 2, 3, and 4 , would that mean that I need 6 helper columns for each of the combinations? What do I then do if I want counts for projects that have a yellow or green status?

I was hoping to have a formula that could work like my report filter that includes any project that has the same value in columns 2, 3 or 4.

Paul Newcome

I can't quite visualize the structure of your sheet. Are you able to provide a screenshot for reference?

And if you are pulling it into a report, you can group the report and then insert a report summary field to count the rows within each group. This can be used to add the data to a chart on a dashboard (but can't be referenced in a metrics widget).

J. Earp

Hi Paul, Above is the datasheet. I want to count all projects that have a Red, Yellow, Green or Blue Status for each COE value, whether it is in the Primary, Secondary or Tertiary Column. Below is the datasheet that I am using for a chart on my dashboard for the ERWB category

And finally, here is the filter I am using on my reports to pull in all the projects:

Not sure if this is helpful or not...

Thank you!

Paul Newcome

Try inserting a multi-select dropdown column and joining each of the extra columns together into a string using CHAR(10) for the delimiter (line break).

Then in your COUNTIFS you would still reference the helper column but use a HAS function on the criteria.

=COUNTIFS({Color Dots}, @cell = "Red", {Helper Column}, HAS(@cell, "ERWB"))

J. Earp

I'll see if I can figure that out. Thank you!

J. Earp

Hi Paul,

Thanks to your suggestions I was able to figure it out late last night. I did create a Helper column that concatenated the values in my primary, secondary and tertiary columns with a space in between. Here is the formula that I then used for my count: =COUNTIFS({2023 HR Projects & Initiatives Master List Range 2}, [Primary Column]@row, {2023 HR Projects & Initiatives Master List Range 3}, OR(CONTAINS("TMEX", @cell), CONTAINS("ALL", @cell)))

Really appreciate your help!

Paul Newcome

Glad you were able to get it sorted. I generally go with the multi-select column / CHAR(10) delimiter / HAS function so I can be more specific in case some options are similar.

Your formula would combine "TMEX" and "TMEXABC" into the same count because they both contain "TMEX". But if you don't have to worry about similar values being separated out like that, then it just boils down to a matter fo preference.