CODEWITHSUNDEEP
excel-2007worksheet-function
David Gard
David Gard

Reputation: 12047

Count the number of values in a range that are not defined in a list

I have a table that contains details of all of our companies mobile phones. Next to this table, I need some basic stats like how many handsets of each OS there are.

Using COUNTIF I can work it all out, apart from Other. Is there a way of calculating the number of values that do not equal anything in a list of values?

This works for just 'not Android' -

=COUNTIF(Mobiles[OS], "<>Android")

But this does not work when trying to exclude all the major players -

=COUNTIF(Mobiles[OS], AND("<>Android", "<>BlackBerry", "<>iOS", "<>Windows"))

Does anybody know how I can achieve this? Thanks.

Upvotes: 0

Views: 73

Answers (2)

pnuts
pnuts

Reputation: 59485

Don’t count Other, instead count All and subtract Specific (as derived from COUNTIF).

Upvotes: 0

zipzit
zipzit

Reputation: 4017

This works, it's just not very clever

=COUNTIFS(Mobile[OS],"<>Android",Mobile[OS],"<>Blackberry", Mobile[OS],"<>iOS",Mobile[OS],"<>Windows",)

Upvotes: 1

Related Questions