CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 736

COUNTIFS with or condition

I have a list of cities (this is a simplified version)

London Manchester Nottingham

I want to say something like (Obviously formula is not correct, is just to show what I am trying to do)

=COUNTIFS(MachineData!$X:$X,"Y",MachineData!$N:$N,"*london*" OR MachineData!$N:$N, "*Manchester*" OR MachineData!$N:$N,"*Nottingham*")

Basically, I want to count if its London OR Manchester OR Nottingham but also if X:X is Y

How can I do this without having to repeat the same instruction 3 times? is there a way to do this where I can tell it to check all the conditions for the cities at once?

Update: I have been trying something like

=COUNTIFS(MachineData!$X:$X,"Y",SUM(COUNTIFS(MachineData!$N:$N,{"London","*Manchester*","Oxford","*Nottingham*"})))

But this is not working for me

Upvotes: 0

Views: 99

Answers (5)

Terry W
Terry W

Reputation: 3257

Suppose you have the following named ranges:

named ranges

There are a couple of ways to crack this question:

Solution1:

=SUMPRODUCT(((N:N=City1)+(N:N=City2)+(N:N=City3))*(X:X=Criteria1))

For this type of question, I normally use SUMPRODUCT as a first instinct, but it is not as fast as using SUM+COUNTIFS if you are working on a large dataset.

Solution2:

=SUM(--ISNUMBER(MATCH(N:N,Cities,0)*MATCH(X:X,Criteria1,0)))

Instead of checking on individual cities, it would be faster to put the cities in a named range and check all of them at once and then add the results up.

Solution3:

=SUM(COUNTIFS(X:X,Criteria1,N:N,TRANSPOSE(Cities)))

This is the solution proposed by JvdV using named ranges.

Solution4:

=SUM((N:N=TRANSPOSE(Cities))*(X:X=Criteria1))

This will do the trick too, but I am not sure how it compares to SUM+COUNTIFS in terms of calculation efficiency on a large dataset.

Solutions

Upvotes: 0

Chris Woodend
Chris Woodend

Reputation: 85

edit: Use @JvdV solution. Far more elegant, less wall of text.

I created a small table as an example, five lines long reading london, manchester, nottingham, cardiff, london. The table name is table1, the column header I used was "target city"

With that in mind, this will bring the desired results into a single cell.

=SUM(COUNTIF(Table1[Target city],"london"),COUNTIF(Table1[Target city],"manchester"),COUNTIF(Table1[Target city],"nottingham"))

Result = 4

Warning: Depending on the length of the target cities you want to bring in and how frequently that list changes this might become prohibitively large/time consuming.

If you need something that will dynamically scale based of a changing list of cities you want to look for, something like python may be a better solution.

Upvotes: 1

Vener Velasco
Vener Velasco

Reputation: 16

enter image description here

=SUM(COUNTIFS(Table2[Category 2],"Y",Table2[Category 1],{"London","Manchester","Nottingham"}))

Upvotes: 0

JvdV
JvdV

Reputation: 75840

You are very close to a solution yourself. Try:

=SUM(COUNTIFS(MachineData!X:X,"Y",MachineData!N:N,{"*london*","*manchester*","*nottingham*"}))

Upvotes: 3

ed2
ed2

Reputation: 1497

No, COUNTIFS is restricted to AND and cannot use OR.

Upvotes: 0

Related Questions