CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 736

COUNTIF option for "Other"

I have data for different cities, Nottingham, London, Derby, Oxford, etc.

and I am doing COUNTIFS that look like this

=COUNTIF(MySheet!$N:$N,"Nottingham")

Basically I want to have a last countif that is "other" so anything that is not on the places I already mentioned then is counted here

=COUNTIF(MySheet!N:N"Every other city")

Basically, how can I do this without having to go <> Nottingham, <>Derby, <>Oxford and like that for all of the cities that I have?

Upvotes: 0

Views: 44

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

The following formula is basically what Rory said:

=COUNTA($N:$N)-COUNTIF(MySheet!$N:$N,"Nottingham")-COUNTIF(MySheet!$N:$N,"Derby")-COUNTIF(MySheet!$N:$N,"Oxford")-XXX

Where XXX = the number of cells in column N that contain
            something that is not a city name such as a 
            column header

Alternatively if you had the list of city names a column headers from P to R and other in column S you could do the following

Place the following in P2 and copy to the right

=COUNTIF(MySheet!$N:$N,P1)

In S2 place the following formula:

=COUNTA($N:$N)-sum(P2:R2)-XXX

Upvotes: 1

Related Questions