# How to count exclusive values in Google Sheets

Ok, so I have data in this format:

``````      A     B     C     D
2    X     X     X
3    X           X
4    X           X     X
5          X
6                X     X
...
``````

I have a formula that helps with a total count per column:

`````` =COUNTA(A2:A)
``````

But how do I calculate “exclusives”? In this case, the expected answer for A, C & D is 0, while B should yield 1 (row 5).

The results will be laid out per column, so my pseudocode logic for column B would be:

`````` if (count(A)=0 and count(C)=0 and count(D)=0)
then count(B)
``````

Alternatively:

`````` count(b) where count(a)=0, count(c)=0, count(d)=0
``````

Is my logic correct in the given context (Google Sheets)? What formula (or combinations of formulae) would I need to achieve the desired result in Google Sheets?

Edit: I'm using named ranges in my sheet, just in case that makes a difference as far as the formula is concerned.

• Edited question title to be more 'organic', as suggested by your link. – Abhishek Jan 21 '15 at 14:56

Assuming that `A` is in Cell A1, flag the rows in which you are interested with, in a spare column (E assumed), something like :

``````=(counta(A2:D2)=1)*1
``````

copied down to suit. Count the rows in each column that are flagged and contain `X` with something like:

`````` =countifs(A1:A10,"X",\$E1:\$E10,1)
``````

in ColumnA and copied cross to suit.

English isn't my first language, I didn't comprehend everything you wrote, but now I think I've got it.

Counting first in columns won't work, counting rows will. Then count the columns.

``````      A     B     C     D     F
2    X     X     X           3*
3    X           X           2
4    X           X     X     3
5          X                 1
6                X     X     2
7    X                       1
8          X     X           2
9          X           X     2

2**
``````

I made it like this, pretty easy I hope.

* Count the X’s in the rows in another column (for example column "F") `=count(B2:E2)` → and drag it down.

** Here we only count the 1’s (so you only count the rows where there is just one "X" `=sumproduct(F2:F=1)`.

I hope this works for you.