How to select number of unique rows of an matrix

  • Thread starter Thread starter MightyKitten
  • Start date Start date
M

MightyKitten

I have a matrix where I need to calculate the number of
Unique selections of a single column

An unique selection of a colum is defined as
This colum is the only one who has an X on that row


Consider this matrix

A B C D E F G
+--------------
1|X X
|
2| X
3|X
4| X X X
5| X
6| X X
7| X
8|1 1 0 0 2 0 0 <-- This row I want to calculate

Unique selections:
B2
A3
E5
E7

Is ther a furmula I could put in a single cell so I can Count this number of
Unique rows in a coulumn?

(oh boy I find this one even quite hard to explane)

Thanks In Advance

MK
 
With a helper column in say Col H.

In Cell H1 put =--(COUNTA(A1:G1)=1) or =--(COUNTIF(A1:G1,"X")=1) depending on
whether blanks really are blanks, or have formulas in them returning blanks. If
real blanks then either will work.

Copy down to H7.

Now in cell A8 put =SUMPRODUCT((A$1:A$7="X")*($H$1:$H$7=1))

and copy across to G8
 
...
...
An unique selection of a colum is defined as
This colum is the only one who has an X on that row ...
Is ther a furmula I could put in a single cell so I can Count this number of
Unique rows in a coulumn?
Click to expand...
...

If your matrix were named MAT and if you wanted to use a single formula to
calculate the results for all columns, select a range in a row below MAT
spanning the same number of columns as MAT and enter the array formula

=MMULT(TRANSPOSE(ROW(MAT))^0,(MMULT(--(MAT="X"),
TRANSPOSE(COLUMN(MAT))^0)=1)*(MAT="X"))

Array formulas are entered by holding down [Ctrl] and [Shift] keys before
pressing [Enter].
 
Thanks I'm gonna try your sugestions. Oh boy, looks a bit like higher Math
to mee. I'm realy amazed that there are even peple who know this stuff.
(Menat that in the ncest way possible)

MightyKitten
 
Thanks I'm gonna try your sugestions. Oh boy, looks a bit like higher Math
to mee. I'm realy amazed that there are even peple who know this stuff.
(Menat that in the ncest way possible)
Click to expand...

My left brain sends its regard to your right brain.
 
Back
Top