# Complex Countif

#### Ark68

##### Well-known Member
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:
Code:
``````        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.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You could try:

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

HTH!

Replies
1
Views
257
Replies
4
Views
414
Replies
0
Views
265
Replies
1
Views
284
Replies
7
Views
562

1,218,695
Messages
6,143,951
Members
450,517
Latest member
Rovex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back