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

ruppk825 · May 17th 2022

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.

Marius44 · May 17th 2022

Hello

Try in B2 and drag down

=COUNTIFS(Sheet2!B3:P3;">0")

Hello,

Mario

ruppk825 · May 17th 2022

Hello -

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

Marius44 · May 17th 2022

Hello

With VBA? Try this macro

Code

Sub CountNonZero()
ur = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To ur
  trg = Sheets("Sheet1").Cells(i, 1).Value
  For j = 3 To ur
    cnt = Sheets("Sheet2").Cells(j, 1).Value
    If trg = cnt Then
      Rng = "B" & j & ":P" & j
      nn = Application.WorksheetFunction.CountIfs(Sheets("Sheet2").Range(Rng), ">0")
      Sheets("Sheet1").Cells(i, 4) = nn
      nn = 0
      Exit For
    End If
  Next j
Next i
End Sub

Display More

Let me know. Hello,

Mario

ruppk825 · May 18th 2022

Thanks, I'll take a look.

bosco_yip · May 19th 2022

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

In B2, formula copied down :

=COUNTIF(OFFSET(INDEX(Sheet2!A:A,MATCH(A2,Sheet2!A:A,0)),,,,1000),">0")

ruppk825 · May 19th 2022

Hello bosco_yip -

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

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

Participate now!

Share

Similar Threads

Select cells with value with the help of a code

Limiting the number of rows

Tags

Users Viewing This Thread