Countifs with and/or not working on multiple criteria

Question

I am trying to get a countifs formula to work where the final criteria is an 'or' statement.

The formula looks like this but is not returning the correct value.

=sum(COUNTIFS('Source Data'!A:A,"Mark", 'Source Data'!B:B,"Approved",'Source Data'!C:C,{"PROCESS", "STRATEGY","Hybrid"}))

I have attached a sheet showing raw data example

https://docs.google.com/spreadsheets/d/1JvTa89tHfv00Vzhn51yTEYwe4-jH5zGL/edit?usp=drivesdk&ouid=112526237791774528521&rtpof=true&sd=true

What is it you expect? Why need to share a file? Can you explain what you mean, so this may also help future visitors to recognize a same issue? — P.b
– P.b, Commented Apr 22 at 11:52
Your data has a <space> after the word Strategy . Hence the rows with that content type will not be counted. Either remove the trailing spaces from your data, or change your formula to reflect the trailing space. — Ron Rosenfeld
– Ron Rosenfeld, Commented Apr 22 at 11:52
In terms of what I expected, I expected the count for Marks approved content to be 4 from my data set. However the formula seems to only be returning "process" only and not the other criteria e.g strategy or hybrid — Raj
– Raj, Commented Apr 22 at 12:05
@Raj that is not shown in your question. The question should show the problem, so future visitors with a similar problem can recognize their problem in yours. — P.b
– P.b, Commented Apr 22 at 20:33

barry houdini · Accepted Answer · 2025-04-22 12:15:21Z

1

As Ron says in the comments you need to remove trailing spaces from "Strategy"....but also your formula is an "array formula", so it will work OK in the latest versions of excel, but in older excel versions it needs to be "array entered" with CTRL+SHIFT+ENTER and in google sheets you need to wrap in "arrayformula" function like this:

=arrayformula(sum(COUNTIFS('Source Data'!A:A,"Mark", 'Source Data'!B:B,"Approved",'Source Data'!C:C,{"PROCESS", "STRATEGY","Hybrid"})))

...or use SUMPRODUCT like this:

=sumproduct(COUNTIFS('Source Data'!A:A,"Mark", 'Source Data'!B:B,"Approved",'Source Data'!C:C,{"PROCESS", "STRATEGY","Hybrid"}))

edited Apr 22 at 12:15

answered Apr 22 at 12:09

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Countifs with and/or not working on multiple criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related