Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for the same serial of characters
Hello,
I have in my spreadsheet as follows: A B C 1 PO200 2 PO200 3. PO201 4. PO202 -------- Total Purchase order = 3 As you can see above the total is only 3 although thre are 4, because PO200 is only 1 for the same serial of characters My question is what is the function in excel the can count for only difference in characters in those cells. Thanks in advance -- H. Frank Situmorang |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for the same serial of characters
hi
you could use the advanced filter. or.... see this site. http://www.officearticles.com/excel/...soft_excel.htm regards FSt1 "Frank Situmorang" wrote: Hello, I have in my spreadsheet as follows: A B C 1 PO200 2 PO200 3. PO201 4. PO202 -------- Total Purchase order = 3 As you can see above the total is only 3 although thre are 4, because PO200 is only 1 for the same serial of characters My question is what is the function in excel the can count for only difference in characters in those cells. Thanks in advance -- H. Frank Situmorang |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for the same serial of characters
Try one of these:
If there will not be any empty cells within the range: =SUMPRODUCT(1/COUNTIF(B1:B4.B1:B4)) If there might be empty cells within the range: =SUMPRODUCT((B1:B4<"")/COUNTIF(B1:B4.B1:B4&"")) Note that either is slow to calculate on large ranges. -- Biff Microsoft Excel MVP "Frank Situmorang" wrote in message ... Hello, I have in my spreadsheet as follows: A B C 1 PO200 2 PO200 3. PO201 4. PO202 -------- Total Purchase order = 3 As you can see above the total is only 3 although thre are 4, because PO200 is only 1 for the same serial of characters My question is what is the function in excel the can count for only difference in characters in those cells. Thanks in advance -- H. Frank Situmorang |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for the same serial of characters
Just a typo alert:
=SUMPRODUCT(1/COUNTIF(B1:B4,B1:B4)) =SUMPRODUCT((B1:B4<"")/COUNTIF(B1:B4,B1:B4&"")) (I changed the dots to commas (my list separator).) "T. Valko" wrote: Try one of these: If there will not be any empty cells within the range: =SUMPRODUCT(1/COUNTIF(B1:B4.B1:B4)) If there might be empty cells within the range: =SUMPRODUCT((B1:B4<"")/COUNTIF(B1:B4.B1:B4&"")) Note that either is slow to calculate on large ranges. -- Biff Microsoft Excel MVP "Frank Situmorang" wrote in message ... Hello, I have in my spreadsheet as follows: A B C 1 PO200 2 PO200 3. PO201 4. PO202 -------- Total Purchase order = 3 As you can see above the total is only 3 although thre are 4, because PO200 is only 1 for the same serial of characters My question is what is the function in excel the can count for only difference in characters in those cells. Thanks in advance -- H. Frank Situmorang -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count for the same serial of characters
Just a typo alert:
(I changed the dots to commas (my list separator).) Thanks, Dave! -- Biff Microsoft Excel MVP "Dave Peterson" wrote in message ... Just a typo alert: =SUMPRODUCT(1/COUNTIF(B1:B4,B1:B4)) =SUMPRODUCT((B1:B4<"")/COUNTIF(B1:B4,B1:B4&"")) (I changed the dots to commas (my list separator).) "T. Valko" wrote: Try one of these: If there will not be any empty cells within the range: =SUMPRODUCT(1/COUNTIF(B1:B4.B1:B4)) If there might be empty cells within the range: =SUMPRODUCT((B1:B4<"")/COUNTIF(B1:B4.B1:B4&"")) Note that either is slow to calculate on large ranges. -- Biff Microsoft Excel MVP "Frank Situmorang" wrote in message ... Hello, I have in my spreadsheet as follows: A B C 1 PO200 2 PO200 3. PO201 4. PO202 -------- Total Purchase order = 3 As you can see above the total is only 3 although thre are 4, because PO200 is only 1 for the same serial of characters My question is what is the function in excel the can count for only difference in characters in those cells. Thanks in advance -- H. Frank Situmorang -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Characters in a cell | Excel Discussion (Misc queries) | |||
count special characters | Excel Worksheet Functions | |||
Macros to count characters | Excel Discussion (Misc queries) | |||
Count characters within a string | Excel Worksheet Functions | |||
Count Characters | Excel Worksheet Functions |