|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 06:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com