|
Question about count ONLY different Texts in a range.
I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
Question about count ONLY different Texts in a range.
=SUM(1/COUNTIF(A1:A7,A1:A7))
This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just the ENTER key -- Gary''s Student - gsnu200795 "ldiaz" wrote: I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
Question about count ONLY different Texts in a range.
If you DON'T want an array formula, you could try:
=SUMPRODUCT((Names<"")/COUNTIF(Names,Names&"")) Where "Names" is the range of cells you want to refer to. Thus, this could also be be: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Cheers Pete "Gary''s Student" wrote: =SUM(1/COUNTIF(A1:A7,A1:A7)) This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just the ENTER key -- Gary''s Student - gsnu200795 "ldiaz" wrote: I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
Question about count ONLY different Texts in a range.
Hi gary, it works OK, but how it will work if I use filter ommiting A-X00-1XR-NST-5222 ? , it should count just 3 Thanks? -- Lorenzo DÃ*az Cad Technician "Gary''s Student" wrote: =SUM(1/COUNTIF(A1:A7,A1:A7)) This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just the ENTER key -- Gary''s Student - gsnu200795 "ldiaz" wrote: I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
Question about count ONLY different Texts in a range.
Thanks, it works perfectly
-- Lorenzo DÃ*az Cad Technician "Gary''s Student" wrote: =SUM(1/COUNTIF(A1:A7,A1:A7)) This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just the ENTER key -- Gary''s Student - gsnu200795 "ldiaz" wrote: I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
Question about count ONLY different Texts in a range.
Thanks Pete, it works perfectly.. Ldiaz -- Lorenzo DÃ*az Cad Technician "Pete Rooney" wrote: If you DON'T want an array formula, you could try: =SUMPRODUCT((Names<"")/COUNTIF(Names,Names&"")) Where "Names" is the range of cells you want to refer to. Thus, this could also be be: =SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&"")) Cheers Pete "Gary''s Student" wrote: =SUM(1/COUNTIF(A1:A7,A1:A7)) This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather than just the ENTER key -- Gary''s Student - gsnu200795 "ldiaz" wrote: I want a formula that can count only text that are different but only one of similar texts, example: -nsk are 2 but the formula must count 1, -nsb is 1 then the formula must count 1, -nst is 3 the formula must count 1, -nst-5222 is 1 the formula must count 1 a total of 4. Count = 4 A-X00-1XR-NSK A-X00-1XR-NSK A-X00-1XR-NSB A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST A-X00-1XR-NST-5222 also, if I use filter ommiting -5222 then the result must be 3 please help, and thanks in advanced. -- Lorenzo DÃ*az Cad Technician |
| All times are GMT +1. The time now is 05:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com