count by rows and columns, unique value only

[tx12345]

Hi

i think this is an easy one, but not so easy for me.

is it possible to count a value in each column per row of the range , and then move on to the next row looking for the same?

for example:

target number: 123 

123 456 345 <-- 123 shows here
123 445 271 <-- 123 shows here
445 445 255 
636 636 300
300 123 123 <-- 123 shows here

so we have three columns wide and 5 rows deep. 123 appears in 3 of the rows (what i want to find) although it appears within the entire range a total of 4 times.

TIA!

tx

 

[Pekkavee]

Try this formula
I turned it from Finnish language into English formula, so I hope it's right.

=IF(SUMPRODUCT(--(A1:A5=A9))=0,0,1)+IF(SUMPRODUCT(--(B5:B5=A9))=0,0,1)+IF(SUMPRODUCT(--(C1:C5=A9))=0,0,1)

In A9 I have 123

Regards
Pekka

 

[Aladin Akyurek]

Hi

i think this is an easy one, but not so easy for me.

is it possible to count a value in each column per row of the range , and then move on to the next row looking for the same?

for example:

target number: 123 

123 456 345<-- 123 shows here
123 445 271<-- 123 shows here
445 445 255 
636 636 300
300 123 123<-- 123 shows here

so we have three columns wide and 5 rows deep. 123 appears in 3 of the rows (what i want to find) although it appears within the entire range a total of 4 times.

TIA!

tx

Book5
	A	B	C	D	E	F	G
1						target	count
2	123	456	345			123	3
3	123	445	271			445	2
4	445	445	255
5	636	636	300
6	300	123	123
7
Sheet1

G2, copied down:

=SUMPRODUCT((MMULT(($A$2:$C$6=F2)+0,ROW($A$2:INDEX($A$2:$A$6,COLUMNS($A$2:$C$6)))^0)>0)+0)

 

[Pekkavee]

Sorry, my formula does not work.
It's counting wrong way.

Pekka

 

[bosco_yip]

Formula in G2 is :

=SUM(--(MMULT(COUNTIF(F2,A$2:C$6),{1;1;1})>0))

 

[tx12345]

Thanks for the responses. The formulas are just what the doctor ordered for the overweight and out of shape spreadsheet

tx