2

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.

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

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.