Count If formula using cross sheet reference

Angela Saunders

I've inherited a massive sheet and leadership has asked that it not be modified. There are 5 "actions" per row. I'm trying to count if any of the 5 actions are classified as "high" significance. The goal is to chart the number of "high" actions by function.

=COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 1}, "High", {DD Input Sheet Range 2}, "High", {DD Input Sheet Range 3}, "high", {DD Input Sheet Range 4}, "high", {DD Input Sheet Range 5}, "high")

Jgorsich

Countifs() has an implied "and" between every criteria - so that formula would actually count only those results where EVERY category is high.

To get a total of "high" values per function, what you'd really need to do is count all entries for that function and then subtract from it all the entries where every category ISN'T high (because of the implied "and" between each criteria). The difference will be the count of entries for that function with ANY amount of "high" entries applied (note - if for a given row there are 3 actions classified as "high", this will only give you a count of 1, not 3).

=COUNTIFS({DD Input sheet - system team function}, "franchise")-COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 1},<> "High", {DD Input Sheet Range 2}, <>"High", {DD Input Sheet Range 3}, <>"high", {DD Input Sheet Range 4},<> "high", {DD Input Sheet Range 5}, <>"high")

If you need the number of "highs" you'll need to split up the formula - count all the "high"s for one activity and then add the next, etc.

=COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 1}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 2}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range3}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 4}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range5}, "High")

Angela Saunders

is using AND/OR better??

=COUNTIFS({DD Input sheet - system team function}, "franchise", AND({DD Input Sheet Range 1}, "High", OR({DD Input Sheet Range 2}, "High", OR({DD Input Sheet Range 3}, "high", OR({DD Input Sheet Range 4}, "high", OR({DD Input Sheet Range 5}, "high"))))))

Jgorsich

Countifs() has an implied "and" between every criteria - so that formula would actually count only those results where EVERY category is high.

To get a total of "high" values per function, what you'd really need to do is count all entries for that function and then subtract from it all the entries where every category ISN'T high (because of the implied "and" between each criteria). The difference will be the count of entries for that function with ANY amount of "high" entries applied (note - if for a given row there are 3 actions classified as "high", this will only give you a count of 1, not 3).

=COUNTIFS({DD Input sheet - system team function}, "franchise")-COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 1},<> "High", {DD Input Sheet Range 2}, <>"High", {DD Input Sheet Range 3}, <>"high", {DD Input Sheet Range 4},<> "high", {DD Input Sheet Range 5}, <>"high")

If you need the number of "highs" you'll need to split up the formula - count all the "high"s for one activity and then add the next, etc.

=COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 1}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 2}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range3}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range 4}, "High")+COUNTIFS({DD Input sheet - system team function}, "franchise", {DD Input Sheet Range5}, "High")

Jgorsich

@Angela Saunders - unfortunately that isn't how or() works.

OR Function | Smartsheet Learning Center

or() can be used inside of an if() statement, where you are evaluating the logical function and then taking action as a result- in that scenario if any of the choices are true it sends you down the "true" branch. Countifs() is ALWAYS treated as an "and" condition instead (it is designed, inherently, to count the scenarios where all noted criteria are found simultaneously). The only way around it is to modify your query to account for that.