| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTA Function
I have the following list. Column A has the below formula and column B
contains a list of Names ColumnA Column B 1 ABCD 2 EFGH 3 IJKL 4 MNOP "" MNOP 5 XYZ I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is copied all the way down Column B. What I need to achieve is that the formula in Column A will increment only for Unique Values of Column B. Therefore when we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row 6 for Name XYZ will show "5" and NOT "6" as I am getting now Thank you all/sgl |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Function
Hi,
Enter a 1 in A1 because that has to be unique then put this in A2 and drag down as far as required =IF(B2<B1,MAX($A$1:A1)+1,"") Mike "sgl" wrote: I have the following list. Column A has the below formula and column B contains a list of Names ColumnA Column B 1 ABCD 2 EFGH 3 IJKL 4 MNOP "" MNOP 5 XYZ I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is copied all the way down Column B. What I need to achieve is that the formula in Column A will increment only for Unique Values of Column B. Therefore when we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row 6 for Name XYZ will show "5" and NOT "6" as I am getting now Thank you all/sgl |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Function
Mike thanks for your quick response.
The formula works well but I had not posted one possibility, that in Column B there may be consecutive Blank Rows and therefore your suggestion counts blank cells since they are identical. In my initial posting if Rows 6 and 7 are Blank, then Row 7 would be counted. I only need to count cells that have values in them. Thanks/sgl "Mike H" wrote: Hi, Enter a 1 in A1 because that has to be unique then put this in A2 and drag down as far as required =IF(B2<B1,MAX($A$1:A1)+1,"") Mike "sgl" wrote: I have the following list. Column A has the below formula and column B contains a list of Names ColumnA Column B 1 ABCD 2 EFGH 3 IJKL 4 MNOP "" MNOP 5 XYZ I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is copied all the way down Column B. What I need to achieve is that the formula in Column A will increment only for Unique Values of Column B. Therefore when we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row 6 for Name XYZ will show "5" and NOT "6" as I am getting now Thank you all/sgl |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Function
Slight change required
=IF(AND(B2<B1,B2<""),MAX($A$1:A1)+1,"") Mike "sgl" wrote: Mike thanks for your quick response. The formula works well but I had not posted one possibility, that in Column B there may be consecutive Blank Rows and therefore your suggestion counts blank cells since they are identical. In my initial posting if Rows 6 and 7 are Blank, then Row 7 would be counted. I only need to count cells that have values in them. Thanks/sgl "Mike H" wrote: Hi, Enter a 1 in A1 because that has to be unique then put this in A2 and drag down as far as required =IF(B2<B1,MAX($A$1:A1)+1,"") Mike "sgl" wrote: I have the following list. Column A has the below formula and column B contains a list of Names ColumnA Column B 1 ABCD 2 EFGH 3 IJKL 4 MNOP "" MNOP 5 XYZ I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is copied all the way down Column B. What I need to achieve is that the formula in Column A will increment only for Unique Values of Column B. Therefore when we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row 6 for Name XYZ will show "5" and NOT "6" as I am getting now Thank you all/sgl |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTA Function
Mike,
Worked very well.Your asistance greatly appreciated. Thank you. sgl "Mike H" wrote: Slight change required =IF(AND(B2<B1,B2<""),MAX($A$1:A1)+1,"") Mike "sgl" wrote: Mike thanks for your quick response. The formula works well but I had not posted one possibility, that in Column B there may be consecutive Blank Rows and therefore your suggestion counts blank cells since they are identical. In my initial posting if Rows 6 and 7 are Blank, then Row 7 would be counted. I only need to count cells that have values in them. Thanks/sgl "Mike H" wrote: Hi, Enter a 1 in A1 because that has to be unique then put this in A2 and drag down as far as required =IF(B2<B1,MAX($A$1:A1)+1,"") Mike "sgl" wrote: I have the following list. Column A has the below formula and column B contains a list of Names ColumnA Column B 1 ABCD 2 EFGH 3 IJKL 4 MNOP "" MNOP 5 XYZ I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is copied all the way down Column B. What I need to achieve is that the formula in Column A will increment only for Unique Values of Column B. Therefore when we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row 6 for Name XYZ will show "5" and NOT "6" as I am getting now Thank you all/sgl |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTA Function | Excel Worksheet Functions | |||
| Counta function | Excel Worksheet Functions | |||
| Output of the =IF(COUNTA) Function | Excel Worksheet Functions | |||
| Using the COUNTA function | Excel Discussion (Misc queries) | |||
| COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions | |||
Linear Mode
