Complex Countif

[Ark68]

I've run into a bit of a hurdle with some logic for some code:

I have a dynamic database, in my example, consisting of 46 rows (row 1 is header). variable lastrow holds the calculated number of rows in the database (46)

Column CW is a result of formula .range("CW" & rnum1) = .range("E" & rnum1) + .range("S" & rnum1). rnum1 is the row number of the current record being worked on. In this example, rnum1 = 2.

The value created will be semi-unique from the other records in the database, and will take a format of DRHPL1 (DR = .range("E2")), and (HPL1 = .range("S2))

This is where I am struggling. In column CX, I need to calculate how many times the value "DR"+that row's value of column S occurs in the entire column CW. In this example, CX2 = the number of occurrances of "DRHPL1" (DR+S2(HPL1) in range CW:CW. CX will be populated for each row in the database unique to it's corresponding value in CW.

Here is my failed attempt:

        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("CX2:CX" & lastrow) = Application.CountIf(.Range("CW:CW"), "DR" + .Range("S" & ???))

I hope I was able to explain myself clearly. It was awkward.

 

[AD_Taylor]

You could try:

        lastrow = .Range("A" & Rows.Count).End(xlUp).Row        
        .Range("CX2:CX" & lastrow) = Application.CountIf(.Range("CW:CW"), .Range("E" & rnum1) & .Range("S" & rnum1))

HTH!