Share via

In Excel I need a formula to convert cell address $BB$2 to a column range BB:BB and use it in another formula

Anonymous
2024-03-14T07:12:38+00:00

I require a formula that can help me to use the column range in COUNTIFS formula. I matched a criterion and got the cell address and now I need to change the cell address to a column range, example $BB$2 to BB:BB. Other formulas I used convert the value in a text format "BB:BB" that cannot work in COUNTIFS formula.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-14T07:58:53+00:00

    =SUBSTITUTE(ADDRESS(2,MATCH(A16,2:2,0),4,1), "2", "") & ":" & SUBSTITUTE(ADDRESS(2,MATCH(A16,2:2,0),4,1), "2", "")

    0 comments
  2. Anonymous
    2024-03-14T07:53:17+00:00

    Thank you. I want to use the column range to count values. I used the following formula to determine the cell address of the value:

    ADDRESS(2,MATCH(A16,2:2,0),4,1)

    This gives me an answer BB2 and now I want to change it to BB:BB so that I can use the range in the COUNTIFS formula. Even if it can be changed to BB2:BB800 then it will work. CONCATENATE creates an answer of "BB:BB" which is seem as a text and makes the COUNTIFS invalid.

    0 comments
  3. Anonymous
    2024-03-14T07:37:08+00:00

    Could you share us the formula you are using? If you changed BB2 to BB:BB, it will get an array with 1048576 rows and you should put the formula in row1.

    I'd suggest you use B2:B10000 instead of entire column reference.

    0 comments