Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to count this in this unusual situation
Hi,
I am trying to count unique value in a list under column E E 436 600 514 548 .. .. .. The value in the above columb B is the result of certain formula such as IF(D9="","",IF(ISNUMBER(MATCH(LEFT(D9,FIND("/",D9)-1),INDIRECT("E"&B$4):E8,0)),"",LEFT(D9,FIND("/",D9)-1))) When I tred to count this using "counta", the reulst was wierd... it returned the number of even blank cell.... So I copy the column and paste it with 'paste special' as value checked to paste only result of the cells. But when pasted, each numbers in the cells has a certain message with ! mark saying " the number in the cell is formatted as text or preceded by an apostrophe". For blank cells, there is no ! mark. But when counting the colum with 'counta', the blank cells are also counted... how to solve this problem? thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to count this in this unusual situation
Try this;
Put this in the next column after you have pasted the result as vaues (assuming you pasted the result in column H from row 1 with no lable) =IF(H1="",0,1) Copy it down and then Sum the column The result of your formula is either returning a number as text or "" which is messing up your attempts to count. "iksuinje" wrote: Hi, I am trying to count unique value in a list under column E E 436 600 514 548 . . . The value in the above columb B is the result of certain formula such as IF(D9="","",IF(ISNUMBER(MATCH(LEFT(D9,FIND("/",D9)-1),INDIRECT("E"&B$4):E8,0)),"",LEFT(D9,FIND("/",D9)-1))) When I tred to count this using "counta", the reulst was wierd... it returned the number of even blank cell.... So I copy the column and paste it with 'paste special' as value checked to paste only result of the cells. But when pasted, each numbers in the cells has a certain message with ! mark saying " the number in the cell is formatted as text or preceded by an apostrophe". For blank cells, there is no ! mark. But when counting the colum with 'counta', the blank cells are also counted... how to solve this problem? thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to count this in this unusual situation
On Mon, 25 Aug 2008 11:48:19 -0700, iksuinje
wrote: Hi, I am trying to count unique value in a list under column E E 436 600 514 548 . . . The value in the above columb B is the result of certain formula such as IF(D9="","",IF(ISNUMBER(MATCH(LEFT(D9,FIND("/",D9)-1),INDIRECT("E"&B$4):E8,0)),"",LEFT(D9,FIND("/",D9)-1))) When I tred to count this using "counta", the reulst was wierd... it returned the number of even blank cell.... So I copy the column and paste it with 'paste special' as value checked to paste only result of the cells. But when pasted, each numbers in the cells has a certain message with ! mark saying " the number in the cell is formatted as text or preceded by an apostrophe". For blank cells, there is no ! mark. But when counting the colum with 'counta', the blank cells are also counted... how to solve this problem? thanks in advance Since you have formulas in the cells, none of them will be "blank". To count the number of numeric values in E1:E10, you could use this: =SUMPRODUCT(--ISNUMBER(-E1:E10)) Note that prior to Excel 2007, you cannot refer to an entire column using this formula. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unusual Printing Problems | Excel Discussion (Misc queries) | |||
Creating an unusual chart | Excel Discussion (Misc queries) | |||
Unusual Request... | Excel Discussion (Misc queries) | |||
Please help with unusual request? | Excel Discussion (Misc queries) | |||
Unusual data format | Excel Worksheet Functions |