Count unique items in a row from a table

[asokaw]

I have to tables, In one one table I need to find out how many times an item exist, but if the item is in duplicate it should take it as 1
Refer the picture - I need to get the answers as shown in RED colour ( these were counted manually and entered )

 

[Domenic]

Try...

H4, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(MMULT(IF($C$4:$E$13=G4,1,0),TRANSPOSE(COLUMN($C$4:$E$13))^0)>0,1))

Hope this helps!

 

[Peter_SSs]

It looks like the first result should actually be 8 since row 13 also contains "GAP"
Another option may be this

21 08 11.xlsm
	C	D	E	F	G	H
1
2
3
4	GAP	GAP	VS		GAP	8
5	VS	M&S	VS		VS	5
6	GAP	DIESEL	GAP		M&S	3
7	GAP	DIESEL	M&S		DIESEL	6
8	GAP	GAP	DIESEL
9	M&S	VS	DIESEL
10	DIESEL	GAP	VS
11	DIESEL	GAP	GAP
12	GAP	GAP	GAP
13	VS	VS	GAP
Count Rows

Cell Formulas
Range	Formula
H4:H7	H4	=SUMPRODUCT(--ISNUMBER(FIND("|"&G4&"|","|"&C$4:C$13&"|"&D$4:D$13&"|"&E$4:E$13&"|")))

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.