Identify Row Number on Different Sheet to complete COUNTIF on (in single formula)

  • Hello -

    Sheet1 is Summary tab and will remain consistent (column A will not re-order, CUSIP will stay in exact positioning month over month).

    Sheet2 is pulled from 3rd party. CUSIP order is subject to change, starting list in column A is subject to change (Column A list starts in row 2 one month, then starting in row 20 the next month etc.).

    On Sheet1/Summary, I'm looking for a formula that can (1) Identify the row number on Sheet2 the same CUSIP is in, and then (2) COUNTIF the number of cells above zero.

    Obviously the COUNTIF above zero part is easy, but I cannot figure out how to identify the corresponding row number on Sheet2 that belongs to the same CUSIP. Any thoughts?

    To summarize: Sheet2 will be re-ordered every month, so I cannot hard link specific row numbers, and need to identify which row number the CUSIP on Sheet1 is on Sheet2 each month. Then do a COUNTIF above zero etc.

  • Hello -

    The CUSIP list on Sheet2 won't always be in the same order. This is the problem I'm attempting to solve.

  • Hello

    With VBA? Try this macro

    Let me know. Hello,


  • Try the formula solution of which the criteria were not in the same order of the source table.

    In B2, formula copied down :


  • Hello bosco_yip -

    Formula above is exactly what I'm looking for. Looks like I need to become more familiar with OFFSET. Thank you!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!