| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I count how many times different texts repeat in a column? | New Users to Excel | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Concatenation 2 Texts | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Linear Mode
