NEED HELP!!!

[Guest]

How to count a character only once?

Ex. Row 1 Collumm A has character "AV" on Row 1 Collumm B has same character
"AV". How do I sum/count only AV once at the end of Row 1. My range is about
6 collumms.

 

[carlo]

How to count a character only once?

Ex. Row 1 Collumm A has character "AV" on Row 1 Collumm B has same character
"AV". How do I sum/count only AV once at the end of Row 1. My range is about
6 collumms.

Found this by using google:

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""),
IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

http://office.microsoft.com/en-us/e...e number of unique values by using functions

hth

Carlo

 

[Guest]

counting empty cells as a unique value:
=SUMPRODUCT(1/COUNTIF(A1:F1,A1:F1&""))

excluding empty cells:
=SUMPRODUCT((A1:F1<>"")/COUNTIF(A1:F1,A1:F1&""))

 

[MartinW]

Hi Chicano,

Not sure of exactly what you want,maybe...

This in G1
=IF(COUNTIF(A1:F1,"AV")>=1,"AV","")
will return AV if AV exists in any of the cells
otherwise blank

This in G1
=IF(COUNTIF(A1:F1,"AV")>=1,1,"")
will return 1 if AV exists in any of the cells
otherwise blank

HTH
Martin